Most common occurrence of a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Archer - 22 Feb 2007 23:19 GMT

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.

Signature

Thanks,
Mike

Reply to this Message

Ron Coderre - 23 Feb 2007 00:05 GMT

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

> Hello.
> I need a worksheet function that will tell me the most frequent occurrence
[quoted text clipped - 11 lines]
> formula in B5 that gives the second most frequent occurence and a forumula in
> C5 that gives the 3rd.

Reply to this Message