How to Auto Delete Duplicate Entries based on a User ID and Date?

Allison Minor

Hello there,

I am new to Smartsheet and trying to figure out how to build a formula to delete duplicate entries based on the "E number" and the "Content for the Week of".

This sheet will be tracking form submissions and I want to delete any duplicate submissions by an individual for a given week.

The screenshot attached has some sample data to test the sheet before I make it live to start collecting data. Ultimately the data set will be too large to manually check for duplications.

Thank you!

2022-07-29_10-18-32.png

Genevieve P.

Hi @Allison Minor

The way I do this in my sheet is to have a helper flag column, just like you have! Then I use a formula to count how many rows that match my conditions appear in the current sheet. If there are more than 1 that match, I flag those rows. This way, each duplicate is flagged and I can manually delete one of the two.

For example:

=IF(COUNTIFS([Content for the Week of]:[Content for the Week of], [Content for the Week of]@row, [E Number]:[E Number], [E Number]@row) > 1, 1, 0)

You can then also set up Conditional Formatting so those rows are highlighted in a certain colour to make them stand out.

Let me know if that helps!

Cheers,

Genevieve

Genevieve P.

Hi @Allison Minor

The way I do this in my sheet is to have a helper flag column, just like you have! Then I use a formula to count how many rows that match my conditions appear in the current sheet. If there are more than 1 that match, I flag those rows. This way, each duplicate is flagged and I can manually delete one of the two.

For example:

=IF(COUNTIFS([Content for the Week of]:[Content for the Week of], [Content for the Week of]@row, [E Number]:[E Number], [E Number]@row) > 1, 1, 0)

You can then also set up Conditional Formatting so those rows are highlighted in a certain colour to make them stand out.

Let me know if that helps!

Cheers,

Genevieve

Allison Minor

@Genevieve P. Thank you so much! That did the trick. I was missing the numbers at the end and adding those made it all come together.

Genevieve P.

No problem! 🙂

The numbers at the end tell the flag what to do - the 1 represents "turned on" and the 0 is "turned off".

Here's another way of looking at the formula:

=IF(COUNTIFS() >1, 1, 0)

or

IF(The COUNT is more than 1, then raise the flag 1, otherwise don't 0)

Cheers,

Genevieve