Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarizing concepts
Hi Guys
I have a column (A) with different concepts repeated several times, what I want is to show those concepts 1 time in column B, ex. Column A Column B Car Car Bike Bike Car Bus Bus Airplane Bus Car Car Bus Bike Bike Airplane Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarizing concepts
1) Add a row at the top of the Sheet and type bla bla as the header in cell A1.
2) Select the whole range and run an "Advanced Filter" while selecting "Only Unique Records". 3) Copy the filtered range and paste into cell B1. Micky "Maracay" wrote: Hi Guys I have a column (A) with different concepts repeated several times, what I want is to show those concepts 1 time in column B, ex. Column A Column B Car Car Bike Bike Car Bus Bus Airplane Bus Car Car Bus Bike Bike Airplane Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarizing concepts
On Dec 30, 3:54*pm, Maracay wrote:
Hi Guys I have a column (A) with different concepts repeated several times, what I want is to show those concepts 1 time in column B, ex. Column A * * * * * * * * * * * * * * * * * * * Column B Car * * * * * * * * * * * * * * Car Bike * * * * * * * * * * * * * *Bike Car * * * * * * * * * * * * * * Bus Bus * * * * * * * * * * * * * * Airplane Bus Car Car Bus Bike Bike Airplane Thanks If you are wanting a dynamic solution then this array formula... IF(ROW($A1)SUMPRODUCT((Concepts<"")/(COUNTIF(Concepts,Concepts)+ (Concepts=""))),"",INDEX(Concepts,SMALL(IF(MATCH(C oncepts,Concepts,0) <ROW(Concepts)-MIN(ROW(Concepts))+1,"",ROW(Concepts)-MIN(ROW (Concepts))+1),ROW($A1)))) entered using Ctrl+Shift+Enter key combination then filled down as far as needed will automatically generate a list of unique concepts. Note that "as is" it requires that the column A list of concepts be a Named Range named "Concepts" (without the quotes). To add the "Concepts" named range you can either select all the concepts then type "Concepts" (without the quotes) into the "Name Box", which is on the left side of the "Formula Bar" then press the Enter key, or you can use the "Define Name" dialog to define "Concepts" as a Dynamic Named Range (DNR). This sample worksheet has "Concepts" as a DNR... http://www.4shared.com/file/18507234..._Concepts.html Alternatively, if you don't want to use a named range then everywhere you see "Concept" mentioned in the formula replace it with the Absolute Row and Column address of the concepts in column A, eg $A$2:$A $11. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarizing concepts
Another simpler? option to frame it up dynamically, using non-array formulas
Assume your source data is in A2 down In B2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW())) In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1)))) Copy B2:C2 down to cover the max expected extent of source data, eg down to C200? Hide/minimize col B. Col C will auto-return the list of uniques from col A as the data in col A changes. Voila? hit the YES below -- Max Singapore --- "Maracay" wrote: Hi Guys I have a column (A) with different concepts repeated several times, what I want is to show those concepts 1 time in column B, ex. Column A Column B Car Car Bike Bike Car Bus Bus Airplane Bus Car Car Bus Bike Bike Airplane Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What are the most important Excel concepts I should know (will be tested soon)? | Excel Discussion (Misc queries) | |||
Summarizing data | Excel Discussion (Misc queries) | |||
What are the most important Excel concepts I should know (will be tested soon)? | Excel Discussion (Misc queries) | |||
FORMULA CONCEPTS | Excel Worksheet Functions | |||
Summarizing data | Excel Discussion (Misc queries) |