worksheet function to summarise a list of text?

[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

 

[Niek Otten]

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

 

[Domenic]

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$22)<=$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$22))),"")

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

Hope this helps!

 

[Guest]

=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

 

[Guest]

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.

 

[ilia]

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.

 

[Guest]

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

 

[Gord Dibben]

It has such a feature.

It is called Data>Filter>Advanced Filter.

Unique Records Only


Gord Dibben MS Excel MVP

 

[jay]

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

 

[Don Guillett]

You could always use a worksheet_change event macro to do this automatically
whenever appropriate cells change.