How to get a dynamic 'most common value'??

[krazykaj]

Hi Guys,

I am stuck…
It seems really easy to do when I look at it, but telling excel to do what I can clearly see is not so fun!!

Basically I have a list of clients (this list changes all the time so the formula needs to be dynamic)
Against each client is a product that they own. What I want to do is quickly find out what product each client owns the most of.


Example:
(the following in cells A1:C23


Client-Product-MainProd
John-apple-?
John-apple-?
John-beans-?
John-apple-?
John-beans-?
Frank-tomato-?
Frank-tomato-?
Frank-squash-?
Frank-squash-?
Frank-tomato-?
Tom-egg-?
Tom-egg-?
Tom-egg-?
Tom-egg-?
Jane-carrots-?
Jane-mango-?
Jane-mango-?
Jane-mango-?
Jane-carrots-?
Jane-mango-?
Jane-carrots-?
Jane-mango-?


So against each Client ID is a product. You can see that in all cases one product will be more common than the rest. So for John it is apples. Sometimes it is the only product a client has. Like for Tom, he only has eggs/

How do I count for each client individually their holdings in each product they own to then get a result of the product with the highest count... ???

So the result in the MajorProdResult should be:


Client-Product-MainProd
John-apple-apple
John-apple-apple
John-beans-apple
John-apple-apple
John-beans-apple
Frank-tomato-tomato
Frank-tomato-tomato
Frank-squash-tomato
Frank-squash-tomato
Frank-tomato-tomato
Tom-egg-egg
Tom-egg-egg
Tom-egg-egg
Tom-egg-egg
Jane-carrots-mango
Jane-mango-mango
Jane-mango-mango
Jane-mango-mango
Jane-carrots-mango
Jane-mango-mango
Jane-carrots-mango
Jane-mango-mango



Hope that makes sense . . .
Is this possible via Formula??
I can probably do it easily via VBA ... but i would like not to use any macro in this instance....

thank you for any time and help you may be able to provide!

Cheers

 

[Marcelo Branco]

Hi,

Maybe this Array-formula in C2

=INDEX($B$2:$B$23,MIN(IF(IF($A$2:$A$23=A2,COUNTIF($B$2:$B$23,$B$2:$B$23))=MAX(IF($A$2:$A$23=A2,COUNTIF($B$2:$B$23,$B$2:$B$23))),ROW($A$2:$A$23)-ROW($A$2)+1)))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

HTH

M.

 

[MrVillareal]

Hi friend

Hope to add some help

Excel Workbook
	A	B	C
1	Client	Product	MainProduct
2	John	apple	John-apple-apple
3	John	apple	John-apple-apple
4	John	beans	John-beans-apple
5	John	apple	John-apple-apple
6	John	beans	John-beans-apple
7	Frank	tomato	Frank-tomato-tomato
8	Frank	tomato	Frank-tomato-tomato
9	Frank	squash	Frank-squash-tomato
10	Frank	squash	Frank-squash-tomato
11	Frank	tomato	Frank-tomato-tomato
12	Tom	egg	Tom-egg-egg
13	Tom	egg	Tom-egg-egg
14	Tom	egg	Tom-egg-egg
15	Tom	egg	Tom-egg-egg
16	Jane	carrots	Jane-carrots-mango
17	Jane	mango	Jane-mango-mango
18	Jane	mango	Jane-mango-mango
19	Jane	mango	Jane-mango-mango
20	Jane	carrots	Jane-carrots-mango
21	Jane	mango	Jane-mango-mango
22	Jane	carrots	Jane-carrots-mango
23	Jane	mango	Jane-mango-mango
Sheet31

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Hope your feedback

 

[krazykaj]

Excellent!!!!

Still trying to figure out what exactly that formula does ... but it works!

Thank you both for you help!!!

 

[MrVillareal]

Yeah Thanks for feedback