Most common occurrence of a string
 

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.


--
Thanks,
Mike

Most common occurrence of a string
 

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


"Mike Archer" wrote:

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.


--
Thanks,
Mike