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.

 

[63falcondude]

Perhaps this will help.
Most Frequently Occurring Word in Excel - Easy Excel Tutorial

 

[63falcondude]

Put this formula in D2

=INDEX(A2:C2,MODE(MATCH(A2:C2,A2:C2,0)))

Hit Ctrl+Shift+Enter instead of Enter to use it as an array formula.

This works if you change the #N/A to N/A

 

[Eric W]

Welcome to the forum.

How about:

	A	B	C	D
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

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

Sheet14​

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

If there are 3 distinct values, it will return the first. It will have to change if you have more than 3 columns. Let me know how this works.

 

[DRSteele]

That's close but it will report that NA is the most frequent. This should avert that condition:

=INDEX(A2:C2,MODE(IFNA(MATCH(A2:C2,A2:C2,0),0)))
CtrlShiftEnter

 

[beatdownkitty]

Put this formula in D2

=INDEX(A2:C2,MODE(MATCH(A2:C2,A2:C2,0)))

Hit Ctrl+Shift+Enter instead of Enter to use it as an array formula.

This works if you change the #N/A to N/A

Unfortunately the values in those three columns are created by other formulas so the #N/A to my knowledge cannot lose the #

Welcome to the forum.

How about:

	A	B	C	D
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>

Sheet14​

Array Formulas Cell Formula D2 {=INDEX(A2:C2,MAX(IF(COUNTIF(A2:C2,A2:C2)>1,COLUMN(A2:C2)-COLUMN(A2)+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>

If there are 3 distinct values, it will return the first. It will have to change if you have more than 3 columns. Let me know how this works.

This throws an error "Moving or deleting cells caused an invalid cell reference"

That's close but it will report that NA is the most frequent. This should avert that condition:

=INDEX(A2:C2,MODE(IFNA(MATCH(A2:C2,A2:C2,0),0)))
CtrlShiftEnter

I tried something very similar to this but it produced #N/A as the most used...your formula does the same thing. Returns #N/A as the most used.

 

[DRSteele]

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.

 

[Eric W]

I converted my test sheet to use formulas to populate the table, including the #N/A cells. My formula still generated the correct answers. How does your sheet differ? Are you using the same rows/columns? Did you delete some rows/columns before the table?

 

[beatdownkitty]

I converted my test sheet to use formulas to populate the table, including the #N/A cells. My formula still generated the correct answers. How does your sheet differ? Are you using the same rows/columns? Did you delete some rows/columns before the table?

My rows/columns are slightly different I made that table as a description of what I had. My three columns are being populated from a VLOOKUP formula on a set of data in another sheet. Each column is pulling data from its own sheet. There is a 4th column "result" which mine is just named "Common Category." This is the cell in which I would be running your formula. I If I hover over the #N/A that is produced using your formula it says there is a circular reference error.

 

[Eric W]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><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)))} <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>

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.