Summarizing concepts

  • Thread starter Thread starter Maracay
  • Start date Start date
M

Maracay

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
 
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
 
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
Click to expand...

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(Concepts,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/185072345/4c2a1b08/Unique_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.
 
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,ROWS($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
 
Back
Top