Re: Top 10 Text Values

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 <03123F44-0812-4752-8058-E363E2DAE0B9@xxxxxxxxxxxxx>,
KarenH <KarenH@xxxxxxxxxxxxxxxxxxxxxxxxx> 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.
.

Relevant Pages

  • Re: Pot doesnt cause long damage!!!!!!!!!!!!!!
    ... I know exactly what studies like this one seek to accomplish. ... Doing a study on the effects of marijuana on people that are not under the influence is similar to doing a study on the effects of alcohol on people that are not drunk. ... potheads seeking to justify there reason for smoking pot. ... choosing things that are good is good... ...
    (alt.gathering.rainbow)
  • Activex Export Multiple files
    ... i would like to accomplish this within an sql dts package using an ... activex script. ... Prev by Date: ...
    (microsoft.public.sqlserver.dts)
  • Copying 2 or more txt files into one.
    ... But I can't figure out how to accomplish that in Opal. ... The execute method keeps giving me an ... Jerry ... Prev by Date: ...
    (comp.databases.paradox)
  • Remove Rows in a List Box after fields are updated
    ... my time constraints I do not want to write code, ... their is no other way to accomplish this task let me know. ... James E ... Prev by Date: ...
    (microsoft.public.access.forms)
  • Re: Pop Quiz
    ... amount to shit and accomplish nothing. ... Prev by Date: ...
    (rec.radio.shortwave)