I have a column of text values in which I need to display the ten most
frequently occurring. I tried setting the AutoFilter and choosing "Top 10",
but it doesn't seem to be doing anything. I checked into some functions that
I thought might work, like Frequency and Rank, but those seem to be for
numbers only. Is there a way I can accomplish this? Thanks.

Assuming that A2:A100 contains your text values, try the following which
will take into consideration ties for 10th place...

B2, copied down:

=IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),COUNTIF($ A$2:$A$100,A2),"")

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1,"")

D1: 10

This indicates that you want a Top 10 list. You can change this as
necessary. For example, if you want a Top 5 list, enter 5 instead.

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(D1,C2:C100,0)) ,C2:C100))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROW()-ROW($F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$100,MATCH(ROW ()-ROW($F$2)+
1,$C$2:$C$100,0)),"")

Hope this helps!

In article ,
KarenH wrote:

I have a column of text values in which I need to display the ten most
frequently occurring. I tried setting the AutoFilter and choosing "Top 10",
but it doesn't seem to be doing anything. I checked into some functions that
I thought might work, like Frequency and Rank, but those seem to be for
numbers only. Is there a way I can accomplish this? Thanks.

Excellent, that works great! Thanks!

"Domenic" wrote:

Assuming that A2:A100 contains your text values, try the following which
will take into consideration ties for 10th place...

B2, copied down:

=IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),COUNTIF($ A$2:$A$100,A2),"")

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1,"")

D1: 10

This indicates that you want a Top 10 list. You can change this as
necessary. For example, if you want a Top 5 list, enter 5 instead.

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(D1,C2:C100,0)) ,C2:C100))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROW()-ROW($F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$100,MATCH(ROW ()-ROW($F$2)+
1,$C$2:$C$100,0)),"")

Hope this helps!

In article ,
KarenH wrote:

I have a column of text values in which I need to display the ten most
frequently occurring. I tried setting the AutoFilter and choosing "Top 10",
but it doesn't seem to be doing anything. I checked into some functions that
I thought might work, like Frequency and Rank, but those seem to be for
numbers only. Is there a way I can accomplish this? Thanks.

What does the CONTROL+SHIFT+ENTER do that ENTER doesnt? I notice it puts
little curly brackets around the formula -- and that if I just enter, the
values don't come out right -- but I'm wondering what exactly that
combination does. Thanks again!


"Domenic" wrote:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(D1,C2:C100,0)) ,C2:C100))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

CONTROL+SHIFT+ENTER is used when dealing with array formulas. For a
detailed explanation, have a look in Excel's help menu under 'Array
Formula'.

In article ,
KarenH wrote:

What does the CONTROL+SHIFT+ENTER do that ENTER doesnt? I notice it puts
little curly brackets around the formula -- and that if I just enter, the
values don't come out right -- but I'm wondering what exactly that
combination does. Thanks again!

Domenic wrote...
Assuming that A2:A100 contains your text values, try the following which
will take into consideration ties for 10th place...

B2, copied down:

=IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),COUNTIF( $A$2:$A$100,A2),"")

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2 )-1,"")

D1: 10

This indicates that you want a Top 10 list. You can change this as
necessary. For example, if you want a Top 5 list, enter 5 instead.

E1:
=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(D1,C2:C100,0) ),C2:C100))-D1

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:
=IF(ROW()-ROW($F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$100,
MATCH(ROW()-ROW($F$2)+1,$C$2:$C$100,0)),"")
....

Ancillary cells aren't necessary.

B2 [array formula]:
=INDEX($A$2:$A$100,MODE(MATCH($A$2:$A$100,$A$2:$A$ 100,0)))

B3 [array formula]:
=INDEX($A$2:$A$100,MODE(IF(COUNTIF(B$2:B2,$A$2:$A$ 100)=0,
MATCH($A$2:$A$100,$A$2:$A$100,0))))

Fill B3 down into B4:B11.

Ancillary cells can make this more efficient, but only additional one
column suffices.

B2:
=COUNTIF(A2:A100,A2)+1

B3:
=IF(COUNTIF(A$2:A2,A3)=0,COUNTIF(A3:A$100,A3)
+ROWS(B3:B$100)/ROWS($A$2:$A$100))

Fill B3 down into B4:B100.

C2:
=INDEX($A$2:$A$100,MATCH(MAX($B$2:$B$100),$B$2:$B$ 100,0))

C3 [array formula]:
=INDEX($A$2:$A$100,MATCH(MAX(IF($B$2:$B$100<VLOOKU P(C2,$A$2:$B$100,2,0),
$B$2:$B$100)),$B$2:$B$100,0))

Fill C3 down into C4:C11.

A couple of issues...

1) It doesn't take into consideration ties for 10th place.

2) Owing to the MODE function, #N/A is returned when values occurring
more than once have been exhausted and only values occurring once remain.

Nevertheless, very interesting Harlan!

In article . com,
"Harlan Grove" wrote:

Ancillary cells aren't necessary.

B2 [array formula]:
=INDEX($A$2:$A$100,MODE(MATCH($A$2:$A$100,$A$2:$A$ 100,0)))

B3 [array formula]:
=INDEX($A$2:$A$100,MODE(IF(COUNTIF(B$2:B2,$A$2:$A$ 100)=0,
MATCH($A$2:$A$100,$A$2:$A$100,0))))

Fill B3 down into B4:B11.

Domenic wrote...
A couple of issues...

1) It doesn't take into consideration ties for 10th place.

Meaning all tied values should be shown?

2) Owing to the MODE function, #N/A is returned when values occurring
more than once have been exhausted and only values occurring once remain.
....

B1 [array formula]:
=INDEX($A$1:$A$500,MATCH(MAX(COUNTIF($A$1:$A$500,$ A$1:$A$500)),
COUNTIF($A$1:$A$500,$A$1:$A$500),0))

B2 [array formula]:
=INDEX($A$1:$A$500,MATCH(MAX(IF(COUNTIF(B$1:B1,$A$ 1:$A$500)=0,
COUNTIF($A$1:$A$500,$A$1:$A$500))),COUNTIF($A$1:$A $500,
IF(COUNTIF(B$1:B1,$A$1:$A$500)=0,$A$1:$A$500)),0))

without including ties. If ties are an issue, then it's much better to
use two columns of formulas to produce the needed results.

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
A couple of issues...

1) It doesn't take into consideration ties for 10th place.

Meaning all tied values should be shown?

Yes. I believe that any solution for a Top N list must show any and all
values tied for Nth place. Otherwise, which of the tied values should
be returned?

Personally, for a Top N list, I prefer the solution I offered. It takes
into consideration ties for Nth place, it doesn't use array formulas,
and is very efficient.

By the way, the solution I offered is courtesy of Aladin Akyurek.

Here's a pretty easy way of doing it, assume the values are in A1:A100 with
a header in A1, sel;ect A1:A100, do datafileradvanced filter, select copy
to another location and where you want the filtered range, select unique
records only and click OK. This will give you a distinct list with the text
values, assume you put them in H1, in the first adjacent cell (in my example
I2) put

=COUNTIF($A$2:$A$100,H2)

copy down as long as needed, now select both columns (H and I) and sort
descending by column I, the first 10 values in H will be the top 10


--

Regards,

Peo Sjoblom






--

Regards,

Peo Sjoblom


"KarenH" wrote in message
...
I have a column of text values in which I need to display the ten most
frequently occurring. I tried setting the AutoFilter and choosing "Top
10",
but it doesn't seem to be doing anything. I checked into some functions
that
I thought might work, like Frequency and Rank, but those seem to be for
numbers only. Is there a way I can accomplish this? Thanks.