# Voting - how do I remove duplicated votes?

Options
edited 09/06/22

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.

• ✭✭✭✭✭✭
Options

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭
Options

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,

best,

www.MVPOPS.com

• Options

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!

• ✭✭✭✭✭✭
Options

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!