Find duplicate rows and Find Max date

Sam Swain

Hello all, I am trying to achieve the following:

I have a list of values, some of them are duplicate. I have a date created column.

I want a formula to find duplicates and then find out if it is the most recent entry compared to its duplicates. From the image below I have the following formulas

Count Duplicate Column
=IF(COUNTIFS(Item:Item, Item@row, [Project Name]:[Project Name], [Project Name]@row) > 1, COUNTIFS(Item:Item, Item@row, [Project Name]:[Project Name], [Project Name]@row))

Duplicate Item and Project Column
=Item@row + " - " + [Project Name]@row

Max Date from Duplicate Column
In this column I was thinking of using the other columns as helpers to find if it is the most recent entry when comparing itself to the duplicates but I know I'm not doing it quite right.

Row 1 is the same as rows 4 and 7. Row 7 is the most recent entry so I want it to return the word "New" as it is the most recent entry.

Please help!

Many thanks,
Sam

Kerry St. Thomas

I had a similar issue; I was noticing that the possibility of two duplicate rows sometimes had the same timestamp too (because multiple people might be working on the same project - not terribly likely, but POSSIBLE.) The way I solved this when doing something similar is to include an Auto Number column (I called it Row ID in the formula) - make sure when you set it up that it only includes numbers. Once that column is there, I collect all the Item/Project names at a row level using the formula, and then compare the Auto Number for that row against the collected info (if the current Row ID is the same as the largest of the collected info, it's marked as "New". This will accommodate marking both single entries and the newest of a duplication as "New" rows. It also addresses those same-timestamp situations.

=IF(MAX(COLLECT([Row ID]:[Row ID], [Item & Project]:[Item & Project], [Item & Project]@row)) = [Row ID]@row, "New", "")

Kerry St. Thomas

I had a similar issue; I was noticing that the possibility of two duplicate rows sometimes had the same timestamp too (because multiple people might be working on the same project - not terribly likely, but POSSIBLE.) The way I solved this when doing something similar is to include an Auto Number column (I called it Row ID in the formula) - make sure when you set it up that it only includes numbers. Once that column is there, I collect all the Item/Project names at a row level using the formula, and then compare the Auto Number for that row against the collected info (if the current Row ID is the same as the largest of the collected info, it's marked as "New". This will accommodate marking both single entries and the newest of a duplication as "New" rows. It also addresses those same-timestamp situations.

=IF(MAX(COLLECT([Row ID]:[Row ID], [Item & Project]:[Item & Project], [Item & Project]@row)) = [Row ID]@row, "New", "")

Sam Swain

Awesome, that worked.

Thank you Kerry!