If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|