Formula to Return Most Common Instance of a Value?

[jehuh]

Hello All,

I've been trying to figure out a clean way to determine and show how frequently a cell value occurred based on another cell. Here is my example below:

For automotive spark plugs, I want to know where spark plug AC_104017 was stocked the majority of the time. The answer is warehouse 1, but i'm struggling to find an easy formula to put the result in column C. I would like to use this formula for a larger list of many different spark plugs.

 

[kweaver]

What happens in a tie?
See if this is what you want or gets you started.

Book4
	A	B	C
1	Plug	Warehouse
2	AC-104017	location1	location1
3	AC-104017	location1	location1
4	AC-104017	store	location1
5	AC-104010	location2	truck
6	AC-104010	truck	truck
7	AC-104010	truck	truck
8	AC-104017	store	location1
9	AC-104017	customer	location1
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=INDEX($B$2:$B$9,MODE(IF($A$2:$A$9=A2,MATCH($B$2:$B$9,$B$2:$B$9,0))))

 

[jehuh]

What happens in a tie?
See if this is what you want or gets you started.

Book4
	A	B	C
1	Plug	Warehouse
2	AC-104017	location1	location1
3	AC-104017	location1	location1
4	AC-104017	store	location1
5	AC-104010	location2	truck
6	AC-104010	truck	truck
7	AC-104010	truck	truck
8	AC-104017	store	location1
9	AC-104017	customer	location1
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=INDEX($B$2:$B$9,MODE(IF($A$2:$A$9=A2,MATCH($B$2:$B$9,$B$2:$B$9,0))))

Thank you for the quick response! In a tie, I would like default to the first instance of the cell

 

[kweaver]

Does my formula do it for you?

 

[jehuh]

Does my formula do it for you?

Yes it looks like that will work. I have have 72,000 lines of data for the formula to work through, but for a smaller sample it worked perfectly. Thank you