Formula for Duplicates by most recent

Hi there!

I have a sheet that is updated daily with new information. Often times information is duplicated and I'm trying to filter that out. I have two helper columns for checkboxes. The first column is set to check the box if a duplicate is discovered. =IF(COUNTIF(Number:Number, Number@row) > 1, 1, 0)

Now I am trying to figure out a formula for the "Old" column that checks the box if the Duplicate column is checked and if the Modified date is not the most recent.

The end result should give me something like this, where the most recent entry (of the duplicates) is not checked.

Any help is greatly appreciated!

• ✭✭✭✭✭✭

You can use the below:

=IF(MIN(COLLECT(Modified:Modified, Number:Number, @cell = Number@row)) = Modified@row, 1, 0)

Please note that because you are using the modified this will constantly change and it may have the same as the other rows...

You can use the created date alternatively:

=IF(MIN(COLLECT(Created:Created, Number:Number, @cell = Number@row)) = Created@row, 1, 0)

• ✭✭✭✭✭✭
edited 12/12/23

Hello @Leibel S

I'm working on something similar but using the date submitted column (created date) and a duplicate column (flag) with a helper column (auto #). I'm having issues getting the formula to work when I combine them - I really only want a Duplicate column (I would rather not have the Use column).

Duplicate column formula: =IF(Status@row = "Not Eligible", 0, IF(COUNTIFS([account_id]:[account_id], @cell = [account_id]@row, Status:Status, @cell <> "Not Eligible", Helper:Helper, @cell <= Helper@row) > 1, 1))

Use column formula: =IF(MIN(COLLECT(Helper:Helper, [account_id]:[account_id], @cell = [account_id]@row)) = Helper@row, 1, 0)

This is an intake sheet from a form (new submissions are added at the top) and we want the new submissions to be flagged as duplicates only if the older entries have a Status of anything but "Not Eligible".

I know it is probably something stupid on my part but I can't wrap my brain around getting this to work. Any assistance/suggestions you could provide would be appreciated.

Thanks Peggy

• ✭✭✭✭✭✭

You can do something like the below which checks against created if the earliest date matching this number does not equal the created date on this row, then it is a duplicate

=IF(MIN(COLLECT(Created:Created, Number:Number, @cell = Number@row)) <> Created@row, 1, 0)

• ✭✭✭✭✭✭

@Leibel S - thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!