Moving only one duplicate using automation

SeanPM

I'm using the following to find duplicates on a sheet:

=IF(COUNTIF(Title:Title, Title@row) > 1, 1, 0)

That's working fine, but I'd like to use automation to move one of the duplicates to another sheet, leaving only 1 unique value in place. At the moment, all I can do is move both duplicate value to another sheet (everything marked with 1).

How can I solve this? Thanks

Jeff Reisman

@SeanPM

Try this:

Add an Auto-Number system column to your sheet, using only numeric values (no prefix, no leading zeroes) and call it "UniqueID".

Add a helper column next to your Duplicate finder column, call it "MaxUnique". In this MaxUnique column, you'll want to find the highest UniqueID number for the Title in question:

=MAX(COLLECT(UniqueID:UniqueID, Duplicate:Duplicate, 1, Title:Title, Title@row))

In English, find the highest UniqueID number where the row is a duplicate (this formula assumes your column for determining duplicate is called "Duplicate") and the Title value is the same as the Title value on this row.

Now add one last helper column called "ToMove" and use this to indicate the column should be moved by an automation rule:

=IFERROR(IF(UniqueID@row = MaxUnique@row, 1, ""), "")

In English, if the row is a duplicate, and it's the highest UniqueID between those duplicates, set this column to 1.

Now comes your automation rule:

Trigger: When rows are added or changed

Condition: Where "ToMove" column is equal to 1

Action: Move row to Duplicate Archive sheet

Once the row moves, on the remaining row, the Duplicate column will change to 0, and the MaxUnique column will go blank.

After you get this working, you can hide your helper columns if desired.

SeanPM

This is incredible - and I can confirm it's working. Thanks.

One question though @Jeff Reisman

My rows are all added to this sheet from other automations in other sheets. When a new row is added (to the bottom), this effectively becomes the most 'up to date' version of the row and I need to delete/move the previous version. If I'm not mistaken, the above will delete the version that has most recently been added. Is there a way I can remove the lowest unique ID rather than the highest?

Also, I've got 3.5k rows but rather than starting at 1, the unique IDs started at 3500 and have gone up to 7000. Why's that??

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318879#Comment_318879

Glad it's working!

I wondered if you were going to want to purge the earlier version rather than the later one. Don't worry, all you have to do is change "MAX" to "MIN" in the formula:

=MIN(COLLECT(UniqueID:UniqueID, Duplicate:Duplicate, 1, Title:Title, Title@row))

You can update the name of the MaxUnique column to MinUnique (any formulas referencing MaxUnique will update the column name automatically behind the scenes.)

Re: the auto-number column, did you create the column, set the values, and then remove the auto-number and start again? If so, it's going to start where it left off numerically before. It shouldn't make a difference, you're really only using it for comparison purposes. You could even hide the column since it technically doesn't have any significance.

SmartsheetUser111

This solution was helpful to me. Thank you @Jeff Reisman.