Skip navigation

Most common word(s) in a list

416 Views 13 Replies Latest reply: Apr 19, 2013 1:32 AM by mtiffen RSS
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.

 

Screen shot 2013-04-18 at 13.20.36.png

  • Wayne Contello Level 6 Level 6 (12,625 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:

    Screen Shot 2013-04-18 at 8.07.22 AM.png

    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)

  • Hiroto Level 5 Level 5 (4,810 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

  • Jerrold Green1 Level 7 Level 7 (28,175 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

  • Wayne Contello Level 6 Level 6 (12,625 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.

  • Hiroto Level 5 Level 5 (4,810 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

  • Hiroto Level 5 Level 5 (4,810 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

  • Hiroto Level 5 Level 5 (4,810 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

  • Jerrold Green1 Level 7 Level 7 (28,175 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

  • Barry Level 7 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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

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.