Logical
Flag original vs. first duplicate vs. repeat duplicates (Expanding COUNTIF)
Use an expanding range COUNTIF($A$2:A2, A2) to distinguish the 1st valid occurrence (=1), the exact 1st duplicate (=2), and 3rd+ repeats (>2).
Why whole-column COUNTIF($A$2:$A$7, A2) > 1 flags the original row too!
An expanding count identifies the first occurrence in the current row order, the second occurrence, and later occurrences. It does not establish which record is authentic or which row is safe to delete.
Sorting changes which row is first. Repeated invoice IDs may represent legitimate line items; compare the full business key before removing records. These sample IDs use letters, digits and hyphens. COUNTIF is case-insensitive and criteria can interpret wildcard characters, so arbitrary identifiers require an appropriate literal-matching policy.
| Row | Invoice ID (A) | Running Occurrence (B) | Audit Classification |
|---|---|---|---|
| Row 2 | INV-101 | 1 | Original (Keep) |
| Row 3 | INV-102 | 1 | Original (Keep) |
| Row 4 | INV-101 | 2 | First Duplicate (=2) |
| Row 6 | INV-101 | 3 | Repeat Duplicate (>2) |
Classify each row into Original, First Duplicate, or Repeat Duplicate
B contains the running count; zero indicates an empty ID. Here Original means first encountered, not verified master record.
=IFS(B4=0,"Missing ID",B4=1,"Original",B4=2,"First Duplicate",B4>2,"Repeat Duplicate")Count repeated IDs and extra occurrences for review
A running count of 2 occurs once for each repeated ID. Counts greater than 1 identify additional occurrences, not automatic deletion candidates. Empty ID cells are assigned zero and excluded.
=COUNTIF(B2:B7, 2)Try the example
Use the original values or change them to test your understanding.