Most common word(s) in a list

Wayne Contello Austin, Texas
mode take numeric values as arguments NOT text. you can make an something like this:
You can hide the light yellow columns and the first row is a header.
B2=IF(IFERROR(MATCH(A2, $A$1:A1, 0), 1)<0, MAX($B$1:B1)+1, "")
select B2 and fill down as needed.
D2=ROW()1
E2=OFFSET($A$1,MATCH(D2,B)1, 0, 1,1)
F2=COUNTIF(A, "="&E2)
select D2 thru F2, then fill down enough rows to allow for the number of colors(or unique words).
G2=OFFSET(E1,MATCH(MAX(F),F, 0)1, 0, 1, 1)

Like (0)


Hiroto Japan
Hello
You may try something like this.
A1 Black A2 Blue A3 Blue A4 Blue A5 Black A6 Black A7 Red A8 Green A9 Black A10 Red B1 =COUNTIF($A$1:$A$10,"="&A1) B2 =COUNTIF($A$1:$A$10,"="&A2) B3 =COUNTIF($A$1:$A$10,"="&A3) B4 =COUNTIF($A$1:$A$10,"="&A4) B5 =COUNTIF($A$1:$A$10,"="&A5) B6 =COUNTIF($A$1:$A$10,"="&A6) B7 =COUNTIF($A$1:$A$10,"="&A7) B8 =COUNTIF($A$1:$A$10,"="&A8) B9 =COUNTIF($A$1:$A$10,"="&A9) B10 =COUNTIF($A$1:$A$10,"="&A10) C1 =LOOKUP(MODE(B1:B10),B1:B10,A1:A10)
C1 will be Black.
Regards,
H

Like (0)


Hiroto,
If you haven't already looked at it, would you please take a look at this discussion.
Thanks,
Jerry

Like (0)


Hiroto I just tried this on my real speadsheet and it gives the wrong answer?
Regards Mark.

Like (0)


Wayne, Your solution works, thanks! Only problem is I havent got a clue what its doing
Regards Mark

Like (0)


Wayne Contello Austin, Texas
Mark,
The portion in the light yellow search for unique words automatically. Then the rest uses those words as arguments to the countif function.

Like (0)


Hiroto Japan
Hello Jerry,
Done post a script though I think Barry has already given the answer.
That sort of spreadsheet had better update itself without depending on external script.
Kind regards,
Hiroto

Like (0)


Hiroto Japan
Hmm. That's strange. It works here as intended. The idea is very simple, so please check the formulae, especially the COUNTIF() carefully. I used absolute cell references in COUNTIF() in order to keep the range from changing in filling down.
H

Like (0)


Hiroto Japan
Oops. Of course the formula in C1 was wrong! Correction is as follows 
C1 =LOOKUP(MAX(B1:B10),B1:B10,A1:A10)
Sorry for the confusion I have made.
Hiroto

Like (0)


H,
OK. I thought you might have written a script to grab colors and place a code in an adjacent column. I seem to remember something like that, but I must be mistaken. Thanks for taking a look at the thread.
Jerry

Like (0)


Hi Mark,
You might also take a look at this thread. I think it's the one Hiroto referred to earlier.
Regards,
Barry

Like (0)


Hiroto
Thanks it now works great. Big thanks again to Wayne, for me personally Hirotos method easier to implement on my spreadsheet as I'm a real novice at the kind of thing.
Regards Mark.

Like (0)


Barry, I'll take a look.
Thanks Mark.

Like (0)
