Mafia Game Vote Tally - Vote Change Disregard Duplicate Values & Summary Count

Using Excel 2010 here.

Hi all, I'm trying to create a spreadsheet to track votes for an online Mafia/Werewolf game. Here's the setup: column A is the voter, column B and C are the two players column A voted for. After all votes are entered, I use a countif function to tally total votes for each player. The two players with the highest votes are removed from game.

I ran into a problem tallying voters who changed their votes. I only want to count their final vote and disregard all their previous entries. I'm thinking of creating a helper column and use index+match function to track their previous votes, but there may be lots of duplicate values for a voter during to multiple vote changes. How do I solve this issue?

Secondly, I'm trying to create a result page that shows the names of all voters who voted for a particular person separated by comma and sorted from earliest to latest. Is there a way to concatenate them into one cell?

For example: Dead Player | 15 votes | Voter 1, Voter 3, Voter 9.... (12 more names)

Thank you so much and happy holidays!

Thanks Glenn, I've attached the spreadsheet for your review.

Column C is the post number in the vote thread, 1 being the earliest post

Column D is the muted player whose votes are not counted in the final tally.

Column E represents each vote.

Column F is placeholder, I thought about =concatenate(e3," (", f3, ")") to combine voter and post number in one.

Column G and H are the two people on each vote

I think I solved the first problem, please refer to Column M. I used lookup function =IFERROR(LOOKUP(2,1/($E$3:$E3=$E4),G$3:G3),""), which returns the last duplicate value.

Column S tracks the final vote for each player.

Now here's the part I couldn't figure out. I want to show who voted for each player (Column TUV etc.), displayed horizontally in chronological order, in multiple cells or in one cell separated by commas. Also I'd like to find a way to reflect the vote changes by each player, for example the changes are struck out. If this is too complex, then I would like to show their final votes only.

Thanks again!

Sorry notation was unclear, my bad. Player 8 was banned and vote 13 discarded, so Player 16 and Player 25 now each receive one less vote.

Updated columns Y to AH. This is similar to what I want, but AC:AH are not sorted chronologically.

Hi AliGW, that's exactly what I did in the file uploaded above your post. Only problem is their order is not arranged chronologically and changes every time I sort the player list.

Fascinating! I see how it's done now and will test further. Thank you so much!

Only one problem remains. Suppose a player votes first but changes vote at the last minute, the returned data will still show the player in the first position, ahead of others who have voted for that person first. Is there a way to track the vote changes in chronological order as well rather than just displaying the final vote? Thanks again.

No problem, thanks a lot for your help.