Finding the Most Occuring Name

[eggsell22]

Example....

A B C
Mike Beth Chris
Chris Sam John
Fred Jacob Sam
Sam Josh Beth

I am trying to figure out how to use excel to look through the table above and find the most occuring name or in math terms (the mode) and list them in order of most occurence to least.

So the result will look like the following.
Sam
Beth
Chris
.
.
.

Any help will be appreciated.
Thanks in advance!

 

[RSXchin]

you could use countif. or a pivot table.

 

[eggsell22]

Not familiar with pivot tables. Didn't know countif could handel txt.

 

[RSXchin]

I'm currently on a machine with 2003 excel... so I'm useless, as I don't understand it's horrible layout. Googlin' might do you good.

 

[eggsell22]

no problem. I am trying to read up on pivot tables at the moment. Thanks for the suggestions.

 

[MikeWx]

Unless you have a large number of names in the three columns, this pivot table solution may be more work than it is worth.
1. To create a single column with all the names, type NAMES in Cell D1, then the following formula in Cell D2:

=INDEX($A$1:$C$10,ROUNDUP(ROW(A1)/3,0),ROW(A1)-(3*(ROUNDUP(ROW(A1)/3,0)-1)))

2. Copy the formula down for as many names as you have. The data I used has ten rows, so I copied it down from D2 through D31. If you have more data, also adjust the $c$10 in the formula to reflect how many rows of data you have.

3. Select Cells D1:D31, click Insert, Pivot Table. I put mine in Cell E1 of the same spreadsheet.

4. From the Pivot Table Field List, drag NAMES down to both the Row Labels and Values boxes. The pivot table will show each name and how many occurrences there are of each name.

5. Right click any of the occurrence values and select Sort, and sub option Sort Largest to Smallest. The namees are now sorted with the most frequent name first.

There may be a more elegant VBA solution, but this one will do the job.