Eliminating duplicate records

TroyDunn

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!

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.

TroyDunn

Thanks for the reply Jeff. In your formula that has the earliest or latest create date, does "Created" refer to the name of the column I would create? I would need to create a formula that would result in "Duplicate" or "Original" based on the results of 3 different columns.

Jeff Reisman

@TroyDunn In this case, Created refers to the system column for created date. If it's not in your sheet, add it. Once you save and refresh, the column will populate automatically with the Created date/time for that row, even for old rows.

(Smartsheet is always recording this info, adding the system column Created just exposes it. Also, as an FYI, keep in mind that while Created shows you the date/time based on the time zone in your regional settings, it's actually recorded in UTC. So if Created shows 5/5/24 10:00 PM, Smartsheet considers that as tomorrow, because it's really recorded as 3/6/24 3:00 AM.)

TroyDunn

I do use a system column called "Modified" that automatically returns the date so I can use that. I'm still having trouble with the formula and here is what I have: =IF(COUNTIF([Project Manager:Project Manager], [Project Manager]@row) > 1, (IF(MIN(COLLECT([Modified:Modified], [Project Manager:Project Manager], [Project Manager]@row)) = [Modified]@row, "Original", "Duplicate")),"") where "Project Manager" refers to a column name in my sheet with text/number properties. I did add a ')' after Duplicate to match all parentheses but I still get an error of Unparseable. I really would like to compare 3 columns and if they all match, remove the duplicates.

Thanks for your help! It has been very beneficial.