Lesson 43 of 45, Errors & checks
Duplicates & reconciliation
Quick answer
Catch repeated rows, and find what is in one list but not the other.
- Key formula
=COUNTIF($A$2:$A$11,A2)(Times this value appears)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
COUNTIF($A$2:$A$11, A2) counts how many times the row's own value appears in the whole list. A count above 1 means the value is duplicated, so =IF(COUNTIF($A$2:$A$11,A2)>1,"Duplicate","") marks every copy.
To mark only the repeats, keep the first one clean, and count only the cells above and including the current row: COUNTIF($A$2:A2, A2)>1. The start is locked and the end moves, the same pattern as a running total.
To reconcile two lists, such as your ledger and the bank statement, count each ledger item in the bank list. A count of 0 means the item is missing from the bank: =IF(COUNTIF($E$2:$E$7,A2)=0,"Not in bank","Matched").
Syntax
=COUNTIF($A$2:$A$11,A2)Times this value appears=IF(COUNTIF($A$2:$A$11,A2)>1,"Duplicate","")Mark every copy=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","")Mark only the later copies=IF(COUNTIF($E$2:$E$7,A2)=0,"Not in bank","Matched")Reconcile against another listWatch out for
- Lock the list you count in: $A$2:$A$11. If it slides down, the counts are wrong.
- COUNTIF treats text and numbers literally, so "INV-1002 " with a trailing space is not a match. Clean the data with TRIM first.
- Matching on the ID is not the same as matching on the amount. For a full reconciliation, check both.
The exercises
- Mark every duplicate. In C2, show Duplicate for any invoice number that appears more than once in A2:A11, and leave it blank (""). Fill down to C11.
- Mark only the repeats. In C2, show Repeat only on the second and later appearances of an invoice number, so the first one stays clean. Fill down to C11.
- Ledger against the bank. In C2, show Matched when the ledger invoice appears in the bank list (E2:E7) and Not in bank when it does not. Fill down to C9.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Mark every duplicate
- Mark only the repeats
- Ledger against the bank