Retrieve most common data in a table - Multiple columns

Jarke

Board Regular
Joined
Aug 13, 2016
Messages
95
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! :confused:
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Jarke

Board Regular
Joined
Aug 13, 2016
Messages
95
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!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
Sounds like you got a solution, but just for curiosity, here's another possibility:

ABCDEFGHIJ
9Most common value
10Fcatcatblue10TexasCarp
11Gpinkdogred1MontanaTuna
12H1catpink14TexasTrout
13ITexasmouseyellow10MaineTuna
14JTunacatpink13Missourigoldfish
15horseyellow14Utahsalmon
16iguanapink1Oregongoldfish
17catred1Texasgoldfish
18iguanapink4Oregontuna
19pigorange1catfish
20cat14
21aardvark1
226
23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet8

Array Formulas
CellFormula
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)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Jarke

Board Regular
Joined
Aug 13, 2016
Messages
95
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top