Retrieve most common data in a table - Multiple columns

[Jarke]

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!

 

[Jarke]

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!

 

[Jarke]

I worked around it, this thread can be removed.

 

[Eric W]

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

	A	B	C	D	E	F	G	H	I	J
Most common value
F	cat	cat	blue	Texas	Carp
G	pink	dog	red	Montana	Tuna
H	cat	pink	Texas	Trout
I	Texas	mouse	yellow	Maine	Tuna
J	Tuna	cat	pink	Missouri	goldfish
	horse	yellow	Utah	salmon
	iguana	pink	Oregon	goldfish
	cat	red	Texas	goldfish
	iguana	pink	Oregon	tuna
pig	orange	catfish
cat
aardvark

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jarke]

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!