Generate Formula that identifies duplicate entries and notates most recent entry

linzc199

I'm trying to create a formula that searches through the serial numbers in a column and if there are duplicates of a serial number, it puts a "yes" or check for whatever duplicate was entered most recently. I have a time/date of entry column, and I also have a duplicate entry column that counts up each time it identifies a duplicate serial number so that the most recent entry has the highest integer. Any help would be appreciated!

Column names: Device Serial Number, Device Serial Number Duplicate Entry, and Row ID (time/date of entry)

Thanks,

Julia Rumburg Price

Okay so here's the deal — you're close @linzc199! The trick is pairing COUNTIF with COLLECT and MAX. Here's the formula:=IF(AND(COUNTIF([Device Serial Number]:[Device Serial Number], [Device Serial Number]@row ) > 1, [Device Serial Number Duplicate Entry]@row = MAX(COLLECT([Device Serial Number Duplicate Entry]:[Device Serial Number Duplicate Entry], [Device Serial Number]:[Device Serial Number], [Device Serial Number]@row ))), "Yes", "")

What's happening:

• COUNTIF checks if that serial number shows up more than once
• COLLECT pulls all the duplicate entry counts for that specific serial number
• MAX finds the highest one
• If THIS row has the highest count? You get a "Yes"

So basically — you're only flagging the row that came in last. 🎯

Pro tip: If you want a checkbox instead of text, just swap "Yes" for 1 and "" for 0.

Let me know if you hit any snags! ~julia