I have a list of text strings, eg
Code: Select all
Fred
Bob
Joe
Fred
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))
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