Pull common word from a set of rows not a column

[beatdownkitty]

I need to obtain the most commonly used value (string) from a set of three rows in 3 category columns and then output this word into the result column. The matching is based off the rows though not the columns. So Row 2's most commonly used word from these three columns is "Marketing"

1	Category 1	Category 2	Category 3	Result
2	Marketing	Marketing	#N/A	Marketing
3	Secure Development	Marketing	Secure Development	Secure Development
4	Secure Development	Secure Development	#N/A	Secure Development

<tbody>
</tbody>

The formula would need to take into account the chance of an #N/A value along with cells containing two words in this case "Secure Development." I've tried a few different things but in no way am I anything close to being great at excel...Result column would be where the most commonly used word is outputted.

 

[beatdownkitty]

Here's basically the same table, but I moved it around a bit. The Result column I moved to the right, so it's separated from the other 3 columns.

	C	D	E	F	G
10	Category 1	Category 2	Category 3		Result
11	Marketing	Marketing	#N/A		Marketing
12	Secure Development	Marketing	Secure Development		Secure Development
13	Secure Development	Secure Development	#N/A		Secure Development

<tbody>
</tbody>

Sheet14​

Array Formulas Cell Formula G11 {=INDEX(C11:E11,MAX(IF(COUNTIF(C11:E11,C11:E11)>1,COLUMN(C11:E11)-COLUMN(C11)+1)))} <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>

The values in C11 to E13 are all the result of VLOOKUPs. Are the columns all adjacent? If they're not, that would require a modification. Also notice that there are 2 ranges in the formula: C11:E11, which is the 3 cells you're comparing on one line. The other is just C11, which is the leftmost cell in the range. Are you entering the formula as an array formula, with Control+Shift+Enter?

If you check all that, and it still doesn't work, let me know. I'm reaching a bit here.

So, I did make one slight error when I tried out your formula, I was not using the leftmost cell in the range. When I do, the formula works okay for cells that do not contain an #N/A but those that do return #N/A as the most used value. The columns are all adjacent to one another in columns B/C/D. Column A has the item in which I am using to pull the values for columns B/C/D. I wish I could share the spreedsheet but I cannot. Column E is by "Common Category" where I am getting the information from. I am entering the equation with Control+Shift+Enter. Here is the equation in the field for the first row.

{=INDEX(B4:D4,MAX(IF(COUNTIF(B4:D4,B4:D4)>1,COLUMN(B3:D4)-COLUMN(B3)+1)))}

Here is the output for the first three rows using your equation:

A	B	C	D	E
Value	Jan Category	Feb Category	Mar Category	Common Category
Value used to pull B/C/D data	Marketing	Marketing	Marketing	Marketing
Value used to pull B/C/D data	Storage	Storage	Sharing	Storage
Value used to pull B/C/D data	#N/A	Storage	#N/A	#N/A

<tbody>
</tbody>

 

[beatdownkitty]

I think #N/A is the key here; it is actually the error condition #N/A and not text. I tested several permutations and the formula with IFNA reports things properly.

You're correct, it is the error condition. I had a formula that I was trying almost exactly as the one you provided but unfortunately neither one are preventing #N/A from being the chosen value when its repeated the most.

 

[Eric W]

OK, I think I see. You want the most common value in the range, but you want to exclude #N/A from consideration. Try this:

	C	D	E	F	G
10	Category 1	Category 2	Category 3		Result
11	Secure Development	Marketing	Marketing		Marketing
12	Secure Development	Marketing	Secure Development		Secure Development
13	#N/A	Secure Development	#N/A		Secure Development
14	Marketing	Marketing	Marketing		Marketing
15	#N/A	#N/A	#N/A		#N/A
16	Marketing	#N/A	Secure Development		Marketing

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

Sheet14​

Worksheet Formulas Cell Formula G11 =INDEX(C11:E11,IF(COUNTIF(C11:E11,NA()),MATCH("*",C11:E11,0),MODE(MATCH(C11:E11,C11:E11,0)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Not an array formula, and only 1 range to change. Let me know if this works.

 

[Eric W]

I just realized that the previous function will return an #N/A if you have 3 distinct non-#N/A values in the range. That may be OK for you. If not, try:

=IFERROR(INDEX(C11:E11,MODE(MATCH(C11:E11,C11:E11,0))),INDEX(C11:E11,MATCH("*",C11:E11,0)))

It works the same as the first in all other cases, but for the 3 different values case, it returns whatever's in the first cell.

 

[beatdownkitty]

OK, I think I see. You want the most common value in the range, but you want to exclude #N/A from consideration. Try this:

	C	D	E	F	G
10	Category 1	Category 2	Category 3		Result
11	Secure Development	Marketing	Marketing		Marketing
12	Secure Development	Marketing	Secure Development		Secure Development
13	#N/A	Secure Development	#N/A		Secure Development
14	Marketing	Marketing	Marketing		Marketing
15	#N/A	#N/A	#N/A		#N/A
16	Marketing	#N/A	Secure Development		Marketing

<tbody>
</tbody>

Sheet14​

Worksheet Formulas Cell Formula G11 =INDEX(C11:E11,IF(COUNTIF(C11:E11,NA()),MATCH("*",C11:E11,0),MODE(MATCH(C11:E11,C11:E11,0)))) <tbody> </tbody>

<tbody>
</tbody>

Not an array formula, and only 1 range to change. Let me know if this works.

Yes that did it! Thank you! I did find an alternative method which was to have an IFNA statement in the VLOOKUPs to set #N/A values to a value of "NoData." This populated the fields for the three columns with "NoData" then my formula for the common category became:

=IF(COUNTIF(B3:D3,"NoData")=2,INDEX(B3:D3,MATCH(MIN(COUNTIF(B3:D3,B3:D3)),COUNTIF(B3:D3,B3:D3),0)),INDEX(B3:D3,MAX(IF(COUNTIF(B3:D3,B3:D3)>1,COLUMN(B3:D3)-COLUMN(B3)+1))))

If NoData is found twice then it assumes the least selection. This only works because I am looking at 3 columns and your way is significantly more simple.

Thanks again!

 

[Eric W]

In Excel, there's always more than one way to skin a cat!

Glad we could help.