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!

Find more posts tagged with

Accepted answers

All comments

Leibel S

@Malsky

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)

Peggy Parchert

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

Leibel S

@Peggy Parchert

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)

Peggy Parchert

@Leibel S - thank you!

mgsmith

@Leibel S I used this formula you posted above:

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

However, some of the ID's are mentioned more than twice. I want the most recent date that the ID was added. How to I adjust this formula?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.