worksheet function to summarise a list of text?

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

Guest

Hello, I have a column filled with a long list of text, with the same entries
repeated several times, i.e.:

golf
soccer
soccer
golf
tennis
golf
biathlon

Is there a worksheet function that lets me specify a range of cells and
returns a list of the text values that appeared in the range, i.e:

golf
soccer
tennis
biathlon


This seems like something a lot of people would want to do so I imagine
there's a function for it, I haven't been able to find it though. Any help
would be appreciated.

Thanks
 
Look here:

http://www.cpearson.com/excel/ListFunctions.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello, I have a column filled with a long list of text, with the same entries
| repeated several times, i.e.:
|
| golf
| soccer
| soccer
| golf
| tennis
| golf
| biathlon
|
| Is there a worksheet function that lets me specify a range of cells and
| returns a list of the text values that appeared in the range, i.e:
|
| golf
| soccer
| tennis
| biathlon
|
|
| This seems like something a lot of people would want to do so I imagine
| there's a function for it, I haven't been able to find it though. Any help
| would be appreciated.
|
| Thanks
 
Try...

C2:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",MATCH("~"&A2:A8,A2:A8&"",0)),ROW(A2:A8)-RO
W(A2)+1),1))

D2, copied down:

=IF(ROWS(D$2:D2)<=$C$2,INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"
",MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A
$2:$A$8)-ROW($A$2)+1),ROWS(D$2:D2))),"")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!
 
=IF(ISERR(SMALL(IF(MATCH(rng,rng,0)=ROW(INDIRECT("1:"&ROWS(rng))),MATCH(rng,rng,0)),ROWS($1:1))),"",INDEX(rng,SMALL(IF(MATCH(rng,rng,0)=ROW(INDIRECT("1:"&ROWS(rng))),MATCH(rng,rng,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed
 
Alternative play to try ..

Either use advanced filter > uniques, or if it is to be dynamic to the
source, then try a play along these lines

Source items assumed running in A1 down

In B1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))

In C1:
=IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))
Select B1:C1, copy down to cover the max expected extent of data in col A,
eg down to row 500? Hide away col B. Col C will return a list of unique items
found in col A. The uniques list will be dynamic to the data in col A as the
data changes.
 
Using your example, try this, array entered in B1:B4 (press Ctrl+Shift
+Enter)

=INDEX($A$1:$A$7,SMALL(IF(MATCH($A$1:$A$7,$A$1:$A
$7,0)=ROW(INDIRECT("1:"&ROWS($A$1:$A$7))),MATCH($A$1:$A$7,$A$1:$A
$7,0),""),ROW(INDIRECT("1:"&ROWS($A$1:$A$7)))))

Advanced filter, as someone else mentioned, is probably your best
solution, because it avoids lengthy formulas and helper columns.
 
Thanks for all your help guys. I found Niek's solution to be the simplest.
Amazing that Excel doesn't provide a function to summarise a list of text
considering all the other obscure stuff it can do.

Jay
 
It has such a feature.

It is called Data>Filter>Advanced Filter.

Unique Records Only


Gord Dibben MS Excel MVP
 
I needed a summary that remained linked to the list, so that when the list
changed the summary changed too. Advanced filter doesn't do this. Thanks for
the suggestion though
 
You could always use a worksheet_change event macro to do this automatically
whenever appropriate cells change.
 
Back
Top