Excel Challenge 37 : Identify Duplicate Transactions

What You Will Learn

Six transaction IDs in A2:A7. Some appear twice — TXN-101 appears on rows 2 and 4, TXN-102 appears on rows 3 and 7. You need to flag each row as DUPLICATE or UNIQUE in column C.

The rule: if an ID appears more than once anywhere in the list, every occurrence is a duplicate:

=IF(COUNTIF($A$2:$A$7, A2) > 1, "DUPLICATE", "UNIQUE")

COUNTIF counts how many times the ID in A2 appears across the full range A2:A7. TXN-101 appears twice, so COUNTIF returns 2. Since 2 > 1, the label is DUPLICATE. TXN-103 appears only once, COUNTIF returns 1, label is UNIQUE.

The dollar signs lock the range. Without them, filling the formula down would shift the search range and miss IDs at the top or bottom.

In this dataset: TXN-101 (rows 2,4), TXN-102 (rows 3,7) are duplicates — 4 DUPLICATE rows. TXN-103 and TXN-104 are unique — 2 UNIQUE rows.

COUNTIF > 1 flags every row whose ID appears more than once.

The audit formulas:

B10: =COUNTA(A2:A7) — total records (6).
B11: =COUNTIF(C2:C7,"DUPLICATE") — duplicate count (4).

Alternative: Expanding Occurrence Count

If you need to track whether an ID appears for the first time vs a repeat, use an expanding range: =COUNTIF($A$2:A2,A2). The first TXN-101 shows 1, the second shows 2.

Going Further: Unique List with UNIQUE

After flagging duplicates, produce a clean list with =UNIQUE(A2:A7,,TRUE) — returns only IDs that appear exactly once.

The >1 rule marks every occurrence of a duplicate ID — not just the second one. TXN-101 on row 2 is flagged as DUPLICATE even though it appears first, because the full-range COUNTIF sees two occurrences. If you only want to flag repeats while keeping the first occurrence as UNIQUE, use the expanding COUNTIF pattern from the duplicate-names challenge.

Note that this checks duplicates in column A only. If two rows have the same ID but different bill amounts, they are still flagged as duplicates. The validation does not consider the amount — it checks the ID alone.