how do I show 1st, 2nd, and 3rd most repeated non-numeric values?

[Guest]

I have several categories that are repeated numerous times. For example,
most eaten fruit in the cafeteria... apples, oranges, bananas...

I want to put in a formula that will count how many times each fruit is
repeated in the data, and if apple is top repeat, show apple in the cell...

Then if banana is second I want to show banana in the next cell. etc.???
Any help?

 

[T. Valko]

Here's the easy way to do this:

Use the Advanced Filter to extract the unique values. Choose to copy those
unique values to another location. Suppose those unique values are copied to
the range A1:A5.

In B1 enter a formula like this:

=COUNTIF(J$1:J$20,A1)

Where J1:J20 is the location of the list with dupes.

Drag copy the formula in B1 down to B5.

Then sort A1:B5 on column B descending.

Biff

 

[Bob Davison]

Here is another way that takes a bit more setup, but works with the data
dynamically. It is not as simple, but it is interesting to see how it
works.

Contain the data to be analyzed in a named-range "data". (Highlight the
cells containing the data and type "data" into the name box and hit
"enter".)

//////////////////

Create a list of the unique items within the data to be counted and ranked.
Select the entire list and make it a named-range called "items".

///////////////////

The four columns to the right of the "items" list will be used for the
calculations.

Select the cells next to the "items" list and name them "count". Select the
top cell in the "count" named-range and name it "countTop".

Copy the following formula into all the cells of the "count" range:

=IF(ROW()-ROW(CountTop)<COUNTA(Items),COUNTIF(Data,Items),"")

The "count" range will now contain a count value for each item in the data
list.

/////////////////////

Next select the cells to the right of the "count" range. Name this range
"RankUnsorted".

Insert the following formula into all the cells of the "RankUnsorted" range:

=IF(ROW()-ROW(CountTop)<COUNTA(Items),RANK(Count,Count)+COUNTIF(CountTop:INDIRECT(ADDRESS(ROW(),COLUMN()-1)),Count)-1,"")

This will rank the count values.

////////////////////

In the next column, copy the following formula in all the cells next to the
ranking values:

=IF(ROW()-ROW(CountTop)<COUNTA(Items),INDEX(Items,MATCH(ROW()-ROW(CountTop)+1,RankUnsorted,0)),"")

This will return the list of items sorted from highest frequency to lowest
frequency.

///////////////////

In the next column, copy the following formula in all the cells next to the
sorted items:

=IF(ROW()-ROW(CountTop)<COUNTA(Items),INDEX(Count,MATCH(ROW()-ROW(CountTop)+1,RankUnsorted,0)),"")

This will return the frequency for each item in the data list.

Note that ties are listed in the order that each item was encountered in the
"items" list.

///////////////////

So, in the end you will have a named-range containing all the data and
seperately a table of five columns. The first 3 columns of the table are
named ranges, the top cell of the 2nd column is a single-cell named range
("countTop"), and the last 2 columns display the output.

The original idea here is not mine, I found something simular and then
modified it.

Bob