Compare duplicate rows, identify most recently added

Stefanie S.

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.

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)

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.