Most common word(s) in a list

415 Views 13 Replies Latest reply: Apr 19, 2013 1:32 AM by mtiffen
Calculating status...
Currently Being Moderated
Apr 18, 2013 5:23 AM

Hi

If there a version of the MODE function that works with text?

For example, Top Color would return the answer Black.

• Level 6 (12,560 points)
Currently Being Moderated
Apr 18, 2013 6:07 AM (in response to mtiffen)

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)

• Level 5 (4,800 points)
Currently Being Moderated
Apr 18, 2013 7:36 AM (in response to mtiffen)

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

• Level 7 (28,120 points)
Currently Being Moderated
Apr 18, 2013 7:54 AM (in response to Hiroto)

Hiroto,

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

Thanks,

Jerry

• Level 6 (12,560 points)
Currently Being Moderated
Apr 18, 2013 8:17 AM (in response to mtiffen)

Mark,

The portion in the light yellow search for unique words automatically.  Then the rest uses those words as arguments to the countif function.

• Level 5 (4,800 points)
Currently Being Moderated
Apr 18, 2013 12:51 PM (in response to Jerrold Green1)

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

• Level 5 (4,800 points)
Currently Being Moderated
Apr 18, 2013 12:59 PM (in response to mtiffen)

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

• Level 5 (4,800 points)
Currently Being Moderated
Apr 18, 2013 1:15 PM (in response to mtiffen)

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

• Level 7 (28,120 points)
Currently Being Moderated
Apr 18, 2013 1:28 PM (in response to Hiroto)

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

• Level 7 (29,095 points)
Currently Being Moderated
Apr 19, 2013 12:36 AM (in response to mtiffen)

Hi Mark,

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

Regards,

Barry

More Like This

• Retrieving data ...

Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.