I have a voting sheet where I am allowing voters to vote as often as they want, but I also want to prevent voters from gaming the vote by voting multiple times for a specific team.
On this Google Sheet: https://docs.google.com/spreadsheets/d/1JMZ1gxtwQbtq8d9mrG2B4jPQvZtViRGDsRiK_-ggdug/edit#gid=0
I have highlighted voters who have all voted more than once for the same team. I need a formula that I able to either:
a) Pick only the last vote of the team.
b) Average out the multiple votes on the same team.
c) Or some other clever method of discarding multiple votes on same team (as long as the equivalent of one vote is counted).
You can see that column K indicates the total vote value per team, however, three teams "Mean Beluga Whales, Brute Infernos, and Red Barracudas) are artificially high from the multiple votes by some individuals.
I need help with column L to create a formula that lists an adjusted value.
Please no pivot tables or filters. I will appreciate your help with creating a suitable formula. :)
Try replacing C3:C in the formulas with INDEX(UNIQUE(B2:C),,2)