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

Options

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!


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!