Compare duplicate rows, identify most recently added

I have a form that brings in graduate student information (ID, name, office, start & end dates, etc). Sometimes the form brings in duplicate information as the student's end date is extended. I'd like to identify the oldest added duplicate to the sheet so that I can create a workflow to move it to my archive sheet. Thank you.

Screenshot 2023-08-25 at 11.08.09 AM.png

Find more posts tagged with

Accepted answers

Carson Penticuff

This should work with only one caveat... if multiple entries have the same date and time (down to the minute), they will not be marked as duplicates.

=IF(AND([Created]@row <> "", NOT([Created]@row = MAX(COLLECT(Created:Created, ID:ID, ID@row)))), 1, 0)

All comments

Carson Penticuff

This should work with only one caveat... if multiple entries have the same date and time (down to the minute), they will not be marked as duplicates.

=IF(AND([Created]@row <> "", NOT([Created]@row = MAX(COLLECT(Created:Created, ID:ID, ID@row)))), 1, 0)

Stefanie S.

This works perfectly - thank you so much!

Carson Penticuff

I'm glad I could help!

mgsmith

@Carson Penticuff I am trying to create a similar formula. I have duplicate ID numbers. I want a formula that will check the box only if their is a duplicate ID and the most recent date of that duplicate ID. When I use the formula above the box is checked for all recent dates regardless of the duplicates.

Carson Penticuff

If you can post a screenshot of your setup ("dummy" data is fine if it is anything confidential, I will be glad to take a look at it.

Paul Newcome

My suggestion (to avoid possible issues with duplicate date/time stamps) is to insert an auto-number column with no special formatting and then reference that instead of the [Created (date)] column.

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.