Retrieve most common data in a table - Multiple columns

Thanks:  0
Likes:  0

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

1. ## 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. ## 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.

3. ## Re: Retrieve most common data in a table - Multiple columns

I worked around it, this thread can be removed.

4. ## 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

5. ## 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!

## User Tag List

#### Posting Permissions

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