Finding the Mode of text strings

[stuckling1]

Hi all-
I have a list of text strings, eg

Code: Select all

Fred
Bob
Joe
Fred

etc

that I want to find the top ten most popular results from.
However, as they are text, the MODE() function, which was the first thing I tried, doesn't work.
The best formula I could find on the web after a quick google is this

Code: Select all

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

from here . But that only seems to find the number one most common, I'd like a top ten, and my with my very rudimentary knowledge of excel I can't tweak this to suit me.

So I came up with the following workaround (possibly quite long-winded or illogical, i don't know) -

Convert from text to numbers, with the results as Unicode numbers/ binary codes or whatever, as long as the output is numerical.
After I've turned the text strings into a numerical hash I then hope to find the top ten results using the MODE() function in the normal way.
Finally, I'd then reverse my original formula to turn the output of the mode formulae (which would be the numerical hash)
back into normal text.

However, I've hit the problem of being unable to find a formula for the most important step - the conversion of text to numerical string. Again, can't find any hints on the web :(

So my question to you wonderful people is, as ever, Any ideas please?

stuckling1

[HansV]

A relatively simple solution:

Make sure that there is a column heading (field name) above the column of text strings.
Create a pivot table based on the list.
Add the field name to both the row labels and to the values area.
Finally, click in any of the cells of the values area, and sort largest to smallest.

When the source data change, you have to refresh the pivot table.

[stuckling1]

Thanks Hans! Great help as always :D

That's a much simpler way to do it, I'll have a go.
If/when I get stuck, I may just be back!

stuckling1

Edit - 20:28 today -

Job's a good 'un !! After a bit of playing around to make sense of Pivot Tables (a new concept to me until 10mins ago) and then following Han's advice, my problem is solved!

[HansV]

Great! Pivot tables are a very powerful tool - they can do a lot of the hard work for you.