2nd and 3rd Modes for Text (not number) entries

[Excelina1234]

Hi All,

I'm trying to find the top 3 most frequently used *words* in a column on my spreadsheet.

Although I can easily find the #1 most frequent word, Top 1, (using Index, Mode, Match), I am stuck when it comes to the top 2 and top 3.

The idea is that I want the formulas for Top 2 and Top 3 to be similar to the "Index, Mode, Match" I am using for Top 1 in the sense that all that they require is one cell with the formula to calculate the results for the chosen range.

For your reference, here is the formula I am using for Top 1:

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

Thank you for your help!

 

[Excelina1234]

Any ideas anyone?

 

[mikerickson]

This CSE formula will return the 2nd most frequent text in the range A1:A8.
Note that there can be no blanks in A1:A8

=INDEX(A:A,MOD( LARGE((MATCH(A1:A8,A:A,0)=ROW(A1:A8))*COUNTIF(A:A,A1:A8)+ROW(A1:A8)/100, 2),1)*100,1)


The MATCH()=ROW() is there to consider only the first occurance of each string.

 

[Excelina1234]

Thanks but this formula doesn't seem to work. It picks out the #1 most frequent entry (i.e. it's a complicated version of the formula above). Any chance you know how to fix this? Thanks!

 

[mikerickson]

Did you enter it with Ctrl-Shift-Enter (Cmd+Return for Mac)?

 

[Excelina1234]

I did, yes! Just realised my mistake (hadn't used A!:A and always used A1:A8). Any chance you could help me find top 3 as well? I just dont understand the top 2 formula well enough in order to be able to create it for top 3. Thanks a lot!

 

[Excelina1234]

Ok, I actually managed (replaced 2 with 3 in the formula). Any chance you could help me make the formula slightly more specific?

Essentially, I want to find the top 2 and top 3 for a particular category. For example: If there are two categories - cats and dogs - I want to see the top 2 and top 3 pedigrees for each category.

I can do the formula for the top 1 (below). However, since I don't understand your formula for top 2 and top 3 (all I know is that it works!), is there a chance you could help me refine it accordingly? Thanks so much!

=INDEX(Pedigree,MODE(IF(Category="Cat",MATCH(Category&Pedigree,Category&Pedigree,0))))

Also, if you have time and could more or less explain how the top 2 and top 3 formulas work, that would be incredible! Thanks so much!

 

[mikerickson]

Just add another filtering term to (MATCH(A1:A8,A:A,0)=ROW(A1:A8))*COUNTIF(A:A,A1:A8)+ROW(A1:A8)/100

something like
(B1:B8="Cat)*(MATCH(A1:A8,A:A,0)=ROW(A1:A8))*COUNTIF(A:A,A1:A8)+ROW(A1:A8)/100

 

[Blunder1]

You could always pivot the data and sort high to low on the count of names..

 

[Excelina1234]

Thanks Mike - that did the job perfectly. Appreciate it!