Most common occurrence of a string

M

Mike Archer

Hello.
I need a worksheet function that will tell me the most frequent occurrence
of a single character string in a range of cells, the second most, and the
third most.
Example
A B C D E F
1
2 A A B B A C
3
4 1st 2nd 3rd
5 A B C

So a formula in A5 that gives the most common occurrence in range A2:F2. A
formula in B5 that gives the second most frequent occurence and a forumula in
C5 that gives the 3rd.
 
R

Ron Coderre

Maybe something like this:

With
A1:F1 containing various text values

A6: 1
B6: 2
C6: 3

The most occurring item is
A5:
=INDEX($A$1:$F$1,1,MATCH(LARGE(FREQUENCY(MATCH($A$1:$F$1,$A$1:$F$1,0),MATCH($A$1:$F$1,$A$1:$F$1,0)),A6),FREQUENCY(MATCH($A$1:$F$1,$A$1:$F$1,0),MATCH($A$1:$F$1,$A$1:$F$1,0)),0))

Copy that formula across thru C5 for the 2nd and 3rd most occurring items

Note: That formula will have problems with ties and blanks.
So if those may exist, let us know so we can account for them.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top