Extract single items from a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is there a way to extract a short list of single items from a longer list of items?

Example: If I have an Excel Sheet with the following cells filled

a
a
a
b
a
c
c
c
d
d
a

I would like to extract a list that says I have: a, b, c and d's in there
Something like an index, without line numbers and so on... just the list

The output would be in four cells

a
b
c
d


Thanks
 
You can do it with a array formula

Enter this formula in a cell

=SUM(IF(D2:D12="a",1,0))

Then Press Ctrl + Shift + Enter keys together

Formula should then look like this

{=SUM(IF(D2:D12="a",1,0))}
 
Try Data > Filter > Advanced Filter and select "Unique
records only" box.

HTH
Jason
Atlanta, GA
 
Is there a way to extract a short list of single items from a longer list of
items?

Example: If I have an Excel Sheet with the following cells filled

a
a
a
b
a
c
c
c
d
d
a

I would like to extract a list that says I have: a, b, c and d's in there
Something like an index, without line numbers and so on... just the list

The output would be in four cells

a
b
c
d
Click to expand...

If the original list were in A1:A11 and the topmost result were in cell C1, then
try the following formulas.

C1:
=A1

C2: [array formula]
=INDEX($A$1:$A$11,MATCH(0,COUNTIF($C$1:$C1,$A$1:$A$11),0))

Select C2 and fill down until it generates #N/A errors.
 
Back
Top