# Duplicates & reconciliation (Excel lesson 43 of 45)

> Catch repeated rows, and find what is in one list but not the other.

Source: https://spreadsheetpractice.com/learn/duplicates-reconciliation-checks · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Errors & checks"
- **Time:** about 11 minutes, 3 graded exercises
- **Functions:** `COUNTIF`, `$`, `Expanding range`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=COUNTIF($A$2:$A$11,A2)`: Times this value appears

## 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

| Formula | What it does |
|---|---|
| `=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 list |

## Watch 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

Solve them in the live grid at [Duplicates & reconciliation](https://spreadsheetpractice.com/learn/duplicates-reconciliation-checks). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **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.
2. **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.
3. **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.

Previous: [Handling errors on purpose](https://spreadsheetpractice.com/learn/errors-iferror-ifna) · Next: [Capstone: sales report](https://spreadsheetpractice.com/learn/capstone-sales-report)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
