Flag first occurrence as “Unique” and others as “Duplicate” by person + date

Laura Walker

Hi Community,

I’m working in a sheet with a bunch of historical data that has these key columns:

• [Personnel Name] (staff contacts)
• Date (date)
• [Duplicate Check] (text, where I want the formula to live)

What I’d like to do is:

• Treat each combination of [Personnel Name] + Date as a group.
• For each person+date group, mark the first occurrence as "Unique" and any additional occurrences as "Duplicate".
• Use this logic as a column formula in [Duplicate Check].

Conceptually, the result should look like this:

Personnel Name	Date	Duplicate Check
Jane Doe	3/28/25	Unique
Jane Doe	3/28/25	Duplicate
Jane Doe	3/29/25	Unique
John Smith	3/28/25	Unique
John Smith	3/28/25	Duplicate

And then I could just filter by that and delete the duplicates.
So I’m looking for ideas or formula patterns that:

• Count or rank occurrences of each person+date across the sheet
• Let the first one evaluate to "Unique" and the rest to "Duplicate"

Has anyone implemented something like this before, maybe using a helper column that only references [Personnel Name] and Date?
Any suggestions or examples would be really helpful.

Paul Newcome

You will need to insert an auto-number column with no special formatting (called "Auto" in this example).

Then your formula in the Unique/Duplicate column would be:

=IF(Auto@row = MIN(COLLECT(Auto:Auto, [Personnel Name]:[Personnel Name], @cell = [Personnel Name]@row, Date:Date, @cell = Date@row)), "Unique", "Duplicate")