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.
Best Answer
-
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)
Answers
-
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)
-
This works perfectly - thank you so much!
-
I'm glad I could help!
-
@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.
-
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.
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!