Voting - how do I remove duplicated votes?

austinov

Dear community,

I am trying to solve a challenge: We have a numerous of affiliates from different locations who want to vote for features and we should prioritize them based on votes.

But, we want to limit voters to give one vote per an affiliate per a feature (Demand_ID). Please, see the screenshot below:

In this example, we'd like to remove rows 2 and 3 since they came from "EU" so these are the duplicates.

Please, share any idea. Thank you in advance!

Jeff Reisman

Add a number-only Auto-number column (no prefix, suffix, or leading zeros) called "RowID". Save the sheet, which will populate this column automatically.

Create a helper column to determine which entries to delete. Can make it a checkbox if you want:

=IF(RowID@row <> MIN(COLLECT(RowID:RowID, [Demand_ID]:[Demand_ID], [Demand_ID]@row, Affiliate:Affiliate, Affiliate@row)), 1, 0)

In English: If the RowID on this row does not equal the minimum value for RowID where the Demand_ID = the Demand_ID from this row and the Affiliate = the Affiliate from this row, check the box, otherwise leave the box unchecked.

This will leave only the earliest "vote" unchecked. If you only want to count the vote entered most recently, just change MIN to MAX.

You can then tell your scoring and counting column formulas to exclude rows where the box is checked.

MVP OPS

You can create an additional Unique check box column, plant the formula in the top column and drag down to bottom of sheet. New rows should be added to the bottom of the sheet.

=IF(COUNTIF(Affiliate$1:Affiliate@row, Affiliate@row) <> 1, 0, 1)

You could then do a sumif formula based on the your unique column being checked.

Hope this helps,

austinov

Hi Brad, thanks a lot for your answer and idea!

The point is besides Affiliates we need to look into Demand_ID too, as we have to search for duplicates only within a unique Demand_ID, so I advanced a bit the formula you offered:

=IF(AND(COUNTIF([Demand_ID]:[Demand_ID], [Demand_ID]@row) <> 1, COUNTIF(Affiliate:Affiliate, Affiliate@row) <> 1), 0, 1).

But, I still have to solve my the bigger part of the task. Having two identical rows, I have to leave as is a first one while discard the second, please see the screenshot:

In this example we have two votes for DMND0012 which came from "Scandinavia" affiliate, and we need to remove or mark as a duplicate the second one.

Any idea how to advance the formula further ?

Thank you!

Jeff Reisman

Add a number-only Auto-number column (no prefix, suffix, or leading zeros) called "RowID". Save the sheet, which will populate this column automatically.

Create a helper column to determine which entries to delete. Can make it a checkbox if you want:

=IF(RowID@row <> MIN(COLLECT(RowID:RowID, [Demand_ID]:[Demand_ID], [Demand_ID]@row, Affiliate:Affiliate, Affiliate@row)), 1, 0)

In English: If the RowID on this row does not equal the minimum value for RowID where the Demand_ID = the Demand_ID from this row and the Affiliate = the Affiliate from this row, check the box, otherwise leave the box unchecked.

This will leave only the earliest "vote" unchecked. If you only want to count the vote entered most recently, just change MIN to MAX.

You can then tell your scoring and counting column formulas to exclude rows where the box is checked.

austinov

Jeff, you are a life-saver! I was thinking towards using COLLECT but couldn't figure out a way to do it. It works perfectly - thank you!

Best Regards,

Anton