Find the top five in list

[Brandon G.]

I have a list of 135 text entries, some of which duplicate. My goal
is to find the most often used entry. For example: Lets say I have a
list:

Apple, Apple, Apple, Orange, Orange, Grape

I want excel to tell me that Apple is the most often found entry,
followed by Orange, and lastly Grape.

Any suggestions???? This is kinda like finding the mode of numerical
values but I want to find the top five or so entries.

Thanks,

Brandon

 

[Guest]

You could use =COUNTIF() formulas, but if you have a very long list a Pivot
Table is easier to construct:

1. Put your list in a column of cells starting with row#2, for example A2
thru A100
2. Put a label in A1

You can make a Pivot Table list each item with the count of the number of
times it appears in the list.


See:


http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[T. Valko]

Here's the easiest way:

Assume your data is in the range A2:A20

Enter this formula in B2 and copy down to B20:

=IF(COUNTIF(A$2:A2,A2)>1,0,COUNTIF(A$2:A$20,A2)-ROW()/10^10)

To extract the most frequent entries enter this formula somewhere, say, E2:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$20,">0"),INDEX(A$2:A$20,MATCH(LARGE(B$2:B$20,ROWS($1:1)),B$2:B$20,0)),"")

Copy down as needed or until you get blanks meaning the data has been
exhausted.

Biff