Link to home
Start Free TrialLog in
Avatar of Doug Van
Doug VanFlag for Canada

asked on

Counting votes but filtering out >1 vote by same person

Hello all,

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. :)

Thank you.
Avatar of Norie
Norie

Doug

Try replacing C3:C in the formulas with INDEX(UNIQUE(B2:C),,2)

=COUNTIF(INDEX(UNIQUE($B$3:$C),,2),I3)

=SUMIF(INDEX(UNIQUE($B$3:$C),,2),I3,$G$3:$G)
Avatar of Doug Van

ASKER

Hi Norie,

Thank you for your effort. It is appreciated even if it doesn't work as expected.

I experimented with your suggestion, I think you did too, in columns N-O? But the suggested formula returns incorrect values.

I am trying to understand how the formula works because, for instance, "Silent Blazers", changes from 272 (correct value) to 300 (incorrect value), another example is "Merry Marvels" changes from the correct 84 to 72, etc.

Whereas the desired values for the false repeaters, are listed in column M, and column N explains what should be counted.


Doug

Are the correct no of votes being returned?

Do you want to exclude the duplicate votes from the adjusted total value?
Hi Norie,
> Are the correct no of votes being returned?
The data in columns A - C would be on-going (pulled with IMPORTRANGE), this is just a sampling of typical data.

>Do you want to exclude the duplicate votes from the adjusted total value?

Almost. I want to exclude all but one of the duplicate votes (Voter (B) + Team voted for (C)). Ideally, I want to keep their last vote made.



In the subscription version of desktop Excel or Excel Online, you now have the dynamic arrays feature. This may be used to tally your votes. Copy down the following formula to return your desired results:
=SUM(IFERROR(XLOOKUP(UNIQUE(B$3:B$1001),FILTER(B$3:B$1001,C$3:C$1001=I3),FILTER(G$3:G$1001,C$3:C$1001=I3),0,,-1),0))

Open in new window

I don't use Google Sheets at all. When I posted the above formula in your Google Sheets workbook, I got 0 for the result. It may be that the formula needs some ArrayFunctions sprinkled in.
Count last vote only.xlsx
Another approach is to use an auxiliary column. I believe this approach would work even in Google Sheets.

The auxiliary column formula determines whether a vote should be counted. If so, it returns 1; otherwise 0. I put this formula in cell H3 and copied it down. Note its use of a test range offset by one row.
=--(COUNTIFS(B4:B$1001,B3,C4:C$1001,C3)=0)

Open in new window

You may then tally votes using this auxiliary column in a simple SUMIFS:
=SUMIFS(G$3:G$1001,C$3:C$1001,I3,H$3:H$1001,1)

Open in new window

Doug

How would you determine which vote(s) to eliminate, or which votes to return?

It might be possible to extend the UNIQUE function to cover the other columns but that wouldn't work as desired with the current data as the values in columns D to F aren't always the same for each team instance.

For example, the values in D to F for Mean Beluga Whales aren't the same in each row - see below.

Team voted for             Meets standards Company values Overall Acceptance
Mean Beluga Whales             4                          4                          6
Mean Beluga Whales             4                          5                          6
Mean Beluga Whales             2                          4                          4

If you were to use UNIQUE on a rang extended to include these other columns then these 3 rows would still be returned.

P.S. Regarding votes being correct, I was referring to the current data.
Hi Norie,

> How would you determine which vote(s) to eliminate, or which votes to return?

I was thinking that only the last vote would be counted. For instance,

adam.xiao@company.comBrute Infernos645100
jesse.jenkins@company.comBrute Infernos666120
theos.linden@att.netBrute Infernos63372
jesse.jenkins@company.comBrute Infernos43476

Jesse.jenkins voted twice so, I would like to only accept the last vote. So the calculation should be 100 + 72 + 76 = 248.
If Jesse.jenkins voted again for Brute Infernos, then 76 would be replaced with the next value.

>P.S. Regarding votes being correct, I was referring to the current data.

The values in column Total Value (K) are the actual unadjusted numbers.
The values in column Adjusted Total Value (L) are not correct. At least not now.
The manually written values in column Should Be (M) are the desired values that I am looking for. Column N shows the (G) values being added.

Thanks again for your effort. :) 

I solved my problem, it works but it is horribly messy. Hopefully, my solution can be improved by someone with better skills than me. :)

Basically I solved the problem by breaking down the requirements into simpler tasks that I could easily apply a formula to solve.

See tab test sheet copy for my monstrosity. Try not to laugh. LOL

I explained each step in row 2, but here it is:

Combine time email address, team vote, vote score (Q, R)
=CONCATENATE(A3,",",B3,";",C3) 
Combine the time to sort in reverse chronological order (see next step). Because for duplicate votes (same voter on same team), I want to accept only the last vote. Note that I am using two delimiting characters so that do two separate splits in subsequent steps.

Sort on date (T, U)
=SORT (Q3:R,1, True, 2,false)
Sort in reverse chronological order.

Split out date (W, X, Y)
The data is sorted, I no longer require the date information.
=split(T3, ",")

Find unique in X (AA, AB)
=sortn(X3:Y,9^9,2,1,TRUE) 
This function will give me the final data that I need for adding the final results. All duplicates (same name on team) are removed, only the last vote of each duplicate remains.

Final Split (email and team) (AD, AE, AF)
Split out email and team name so that teams can be matched against Team List in column (AH)      

Columns AH - AJ are the same as in the original sheet. Column AK it the manually calculated results, used to validate that column AJ is correct.


Now, my question is, can all those steps be reduced with more clever formulas?

Thanks for your help. :) 
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much byundt. Simply brilliant! :)


And thank you to Norie, as well. :)