How to Auto Delete Duplicate Entries based on a User ID and Date?
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!
Best Answer

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
Answers

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. Thank you so much! That did the trick. I was missing the numbers at the end and adding those made it all come together.

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
Help Article Resources
Categories
Check out the Formula Handbook template!