SOLVED

Find the Mode of Row Values Based on ID Column (Favorite Fruits)

Occasional Contributor

Hello all,

 

I am trying to find a way to determine everyone's favorite fruit. This is the table that I have:
ID          Name       Fruit Eaten

1JoeBanana
1JoeBanana
1JoeOrange
2SteveApple
2SteveApple
3CarolBanana
3CarolOrange
3CarolApple
3CarolOrange
3CarolOrange

 

As you can see, not everyone has eaten the same number of fruit. Now imagine there are hundreds or even thousands of people on this list. How might I determine everyone's favorite fruit?

 

Not sure if this will be helpful, but this is what I have added to the table as I am brainstorming:

ID      Name    Fruit        Check  Count  ID2

1JoeBanana 1 
1JoeBanana 1 
1JoeOrangeCheck21
2SteveApple 1 
2SteveAppleCheck22
3CarolBanana 1 
3CarolOrange 2 
3CarolApple 3 
3CarolOrange 4 
3CarolOrangeCheck53

Check=IF(A3=[@ID],"","Check")
Count=IF(D1="Check",1,IF(Table1[@Check]="",E1+1,E1))
ID=IF(D2="Check",[@ID],"")

3 Replies
Best Response confirmed by rhuen (Occasional Contributor)
Solution

@rhuen Why not try a pivot table.

 

See attached.

 

@rhuen 

Riny's suggestion is the easiest, but if you prefer a formula, see the attached.

Perfect, thank you! This is actually what I will use because there are too many "fruits" in the actual table I am using this on for a pivot table.