Feb 03 2021 07:18 AM
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],"")
Feb 03 2021 07:36 AM
SolutionFeb 03 2021 07:58 AM
Riny's suggestion is the easiest, but if you prefer a formula, see the attached.
Feb 03 2021 08:08 AM - edited Feb 03 2021 08:34 AM
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.
Feb 03 2021 07:36 AM
Solution