Get second most frequent string

Question

Hi there.

If I have on my column A this values:

Black

Black

Purple

Purple

Grey

Grey

Grey

And on B1 place:

=INDEX($A$1:$A$8;MODE(MATCH($A$1:$A$7;$A$1:$A$7;0)))

I will get Grey.

But if I want to get the second most frequent? How can I achieve that using just formulas?

Regards,

kodiak

Answer 1

Hi,

In theory this can go and and return the 1st, 2nd, third etc but as you will see by the time we get to the third it's all getting a bit silly and it's time to resort to VB code. However:-

All ARRAY formula

First

=INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0))))

Second

=INDEX(A1:A100,MODE(IF((A1:A100<>"")*(A1:A100<>INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0))))),MATCH(A1:A100,A1:A100,0))))

Third

=INDEX(A1:A100,MODE(IF(((A1:A100<>"")*(A1:A100<>INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0)))))*(A1:A100<>INDEX(A1:A100,MODE(IF((A1:A100<>"")*(A1:A100<>INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0))))),MATCH(A1:A100,A1:A100,0)))))),MATCH(A1:A100,A1:A100,0))))

Answer 2

Based on some Googling I did, here is one possible route.  It requires on extra column - you can't go directly to the text label, so the middle column is a "helper" that holds the cell number of the most frequent, second most frequent, etc, entry.

If you have in column A this data:

Black

Black

Black

Purple

Purple

Grey

Grey

Grey

Grey

Then in cell B1 place this formula to get the most frequent item:

=MODE(MATCH($A$1:$A$9,$A$1:$A$9,0))

Place the formula below in cell B2 to show the second most frequent.  You must make this an ARRAY FORMULA by pressing SHIFT-CTRL-ENTER instead of just ENTER:

=MODE(IF(COUNTIF($B$1:$B1,MATCH($A$1:$A$9,$A$1:$A$9,0))=0,MATCH($A$1:$A$9,$A$1:$A$9,0)+{0,0}))

Now you can drag that formula down to cell B2 to get the third most frequent, etc:

=MODE(IF(COUNTIF($B$1:$B2,MATCH($A$1:$A$9,$A$1:$A$9,0))=0,MATCH($A$1:$A$9,$A$1:$A$9,0)+{0,0}))

Finally, in Column C, cells C1, C2 and C3, place the formulae below.  Note again that the second two are ARRAY FORMULAS!

=INDEX($A$1:$A$9,MODE(MATCH($A$1:$A$9,$A$1:$A$9,0)))

=INDEX($A$1:$A$9,MODE(IF(COUNTIF($B$1:$B1,MATCH($A$1:$A$9,$A$1:$A$9,0))=0,MATCH($A$1:$A$9,$A$1:$A$9,0)+{0,0})))

=INDEX($A$1:$A$9,MODE(IF(COUNTIF($B$1:$B2,MATCH($A$1:$A$9,$A$1:$A$9,0))=0,MATCH($A$1:$A$9,$A$1:$A$9,0)+{0,0})))

The result should look like this:

Black	6	Grey
Black	1	Black
Black	4	Purple
Purple
Purple
Grey
Grey
Grey
Grey

HTH,

Eric

P.S. - Someone smarter than me can probably fit this solution into a single column instead of two!

Answer 3

Your query forced me to think if I can get the n-th most frequent value.

Well, I have worked this out. Worked for my sample.

=INDEX(data,MATCH(MAX(LARGE(((IF((ROW(INDIRECT("1:"&ROWS(data))))=(MATCH(data,data,0)),COUNTIF(data,data),0))+((100000-(ROW(INDIRECT("1:"&ROWS(data)))))/100000)),desiredRank),1),((IF((ROW(INDIRECT("1:"&ROWS(data))))=(MATCH(data,data,0)),COUNTIF(data,data),0))+((100000-(ROW(INDIRECT("1:"&ROWS(data)))))/100000)),0))

Note:

1. This needs to be entered as an array formula with Ctrl+Shift + Enter.
2. Name your data range as "data" or alternatively, replace the term "data" in the above formula with your range (eg. $A$1:$A$7)
3. Replace the term desiredRank with 1 or 2 or 3 or 4 or 5 as you may need or with a cell reference (like C1) for a dynamic working formula.
4. It has been written in a way that it should work for any rank. Further, I have incorporated a system whereby if two items have the same frequency, the one coming first in the data list is ranked higher and the one coming second is ranked next.
5. Will not work for more than 100000 rows of data. However to change this limit, you can add or remove 0's across the formula. i.e. You can reduce it to 1000 or increase it to 10000000 wherever the figure appears in the formula.
6. Caution: Use on large data at your own risk. Looks Pretty complicated in the end.

Answer 4

What would the formula for the 4th and 5th be? Sorry, I don't quite understand what these formulas are doing, but they work :)

Answer 5

I managed to compress my formula to use just one column:

Assume your list is in Column A, A2:A5001 (5000 items)

In cell B2, use this formula:

=OFFSET($A$1,MODE(MATCH($A$2:$A$5001,$A$2:$A$5001,0)),0)

In cell B3, use this array formula:

=OFFSET($A$1,MODE(IF(COUNTIF($B$2:$B2,$A$2:$A$5001)=0,MATCH($A$2:$A$5001,$A$2:$A$5001,0)+{0,0})),0)

Don't forget to use SHIFT-CTRL-ENTER.

Drag cell B3 down for however many levels you want - top 3, top 10, top 25 number of occurrences.

HTH,

Eric

P.S. - This formula correctly picks up ties, so that if you do a 'top 10' list, and number 6, 7, 8 are tied, you won't see the same text string three times - you'll see each individual string, in the order it first appears in the list.