Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Retrieve most common data in a table - Multiple columns

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Retrieve most common data in a table - Multiple columns

    Hi!

    I have data in f10:O1000 and I want to pull out the most common data within this range. Notice it's F to O so not just one column that I want as a criteria.

    The values will not always be the same on each row so I would probably haft to check each column entirely in a step by step fashion. Perhaps use a count function. This should all be in one formula, in which I can drag down and it will fill up each row, if the value exists in a minimum of X columns. Something like the formula below:

    =IFERROR(INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5>0,IF($C$2:$C$5>10,ROW($A$2:$A$5)-ROW($A$2)+1)),ROWS($A$2:A2))),"")

    I don't know how to put in the Index or match function tho :/.


    So my goal is the create a list in which it returns the most common values that are in each column. It will only be one of each value in every column, so I'm basing the criteria on how many columns the value are in.


    Appreciate all help!

  2. #2
    Board Regular
    Join Date
    Aug 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve most common data in a table - Multiple columns

    So I found a code and reworked it little:

    =INDEX($F$11:$F$1500, MATCH(0, COUNTIF($B$10:B10, $F$11:$F$1500)+IF(IF(COUNTIF($G$11:$G$1500, $F$11:$F$1500)>0, 1, 0)+IF(COUNTIF($H$11:$H$1500, $F$11:$F$1500)>0, 1, 0)=2, 0, 1), 0))

    Anyone who can help me to add the rest of the columns? It contains three finished but don't really know how to continue smoothly. I want it to go to "O", ten columns in other words.

    Thanks in advance!

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve most common data in a table - Multiple columns

    I worked around it, this thread can be removed.

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,511
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Retrieve most common data in a table - Multiple columns

    Sounds like you got a solution, but just for curiosity, here's another possibility:

    A B C D E F G H I J
    9 Most common value
    10 F cat cat blue 10 Texas Carp
    11 G pink dog red 1 Montana Tuna
    12 H 1 cat pink 14 Texas Trout
    13 I Texas mouse yellow 10 Maine Tuna
    14 J Tuna cat pink 13 Missouri goldfish
    15 horse yellow 14 Utah salmon
    16 iguana pink 1 Oregon goldfish
    17 cat red 1 Texas goldfish
    18 iguana pink 4 Oregon tuna
    19 pig orange 1 catfish
    20 cat 14
    21 aardvark 1
    22 6
    23
    Sheet8

    Array Formulas
    Cell Formula
    B10 {=IFERROR(INDEX($F$10:$O$1000,MODE(IF(INDEX($F$10:$O$1000,0,ROWS($B$10:$B10))<>"",MATCH(INDEX($F$10:$O$1000,0,ROWS($B$10:$B10)),INDEX($F$10:$O$1000,0,ROWS($B$10:$B10)),0))),ROWS($B$10:$B10)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    Board Regular
    Join Date
    Aug 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve most common data in a table - Multiple columns

    Hi Eric, thanks for your reply. It's not quite what I had in mind but seems like a good solution if you seek the most common value in a column. I went with a COUNTIF function, that way I could also filter by most common value. My goal was to find most common value in all columns together, not one by one.

    Anyway, thanks for the input!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •