Forum Discussion
Find the Mode of Row Values Based on ID Column (Favorite Fruits)
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
| 1 | Joe | Banana |
| 1 | Joe | Banana |
| 1 | Joe | Orange |
| 2 | Steve | Apple |
| 2 | Steve | Apple |
| 3 | Carol | Banana |
| 3 | Carol | Orange |
| 3 | Carol | Apple |
| 3 | Carol | Orange |
| 3 | Carol | Orange |
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
| 1 | Joe | Banana | 1 | ||
| 1 | Joe | Banana | 1 | ||
| 1 | Joe | Orange | Check | 2 | 1 |
| 2 | Steve | Apple | 1 | ||
| 2 | Steve | Apple | Check | 2 | 2 |
| 3 | Carol | Banana | 1 | ||
| 3 | Carol | Orange | 2 | ||
| 3 | Carol | Apple | 3 | ||
| 3 | Carol | Orange | 4 | ||
| 3 | Carol | Orange | Check | 5 | 3 |
Check=IF(A3=[@ID],"","Check")
Count=IF(D1="Check",1,IF(Table1[@Check]="",E1+1,E1))
ID=IF(D2="Check",[@ID],"")
3 Replies
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.
