Find most common occurring text

[scorp]

I need to count the text that occurs the most in a column and return the word

For example a1 has dog a2 has cat a3 has dog

I need b1 to return dog as it's the most common occurence

 

[Domenic]

Assuming that A2:A10 contains the data, try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(A2:A10,MODE(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0))))

However, since there can be more than one value tied for the most common occurring text, the following may be more appropriate...

B2, copied down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF(A2:$A$10,A2),"")

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1,"")

D1: 1

(This indicates that you want the top most common occurring text. This can be changed as desired. For example, for the Top 3, enter 3 instead.)

E1:

=MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2:C10))-D1

...confirmed with CONTROL+SHIFT+ENTER.

F2, copied down:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX($A$2:$A$10,MATCH(ROWS(F$2:F2),$C$2:$C$10,0)),"")

Hope this helps!

 

[scorp]

Perfection, thank you