Eliminating duplicate records

Is there any way to eliminate duplicate records automatically on a sheet? I have rows copied to a sheet under certain conditions but only want it to copy those rows once when they are identical. Since there is no 'Duplicate' action for an automation rule, I can't figure out a way to do this.

Thanks for any help!

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @TroyDunn This is a tough one. I use a helper column to identify rows with duplicate values:

    =COUNTIF(ColumnA:ColumnA, ColumnA@row)

    You could also add in some formula to tell you which has the earliest or latest create date:

    =IF(COUNTIF(ColumnA:ColumnA, ColumnA@row) > 1, (IF(MIN(COLLECT(Created:Created, ColumnA:ColumnA, ColumnA@row)) = Created@row, "Original", "Duplicate"), "")

    Obviously, check parentheses in the above... But it should put "Original" in the first of any duplicate rows, "Duplicate" in any subsequent duplicate rows, and leave all unique rows blank.

    Then set a condition in your automation to exclude any rows with "Duplicate" in the helper column.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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