Duplicate checker template
By the Analistable team · Updated · 2 min read
The workbook has a List sheet and a Summary. Paste your rows with the value to check (such as email) in column C; formulas trim stray spaces, count how often each value appears, mark the first occurrence Keep and later ones Remove duplicate, and the Summary reports total rows, unique values and rows to remove.
Part of our guide: How to compare two spreadsheets for differences and matches
What the template does
Excel's Remove Duplicates button deletes rows immediately and shows no reasoning. Before you delete anything from a customer or contact list, you usually want to see which rows repeat, how many times, and which copy survives. This template marks every row instead of deleting it, so you can review, then filter and delete. It applies the techniques in combining duplicate rows and merging contact lists without duplicates.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| List | Row ID, Name, Email (or any key) | Clean key, Times seen, Occurrence, Action |
| Summary | Nothing | Total rows, unique values, values repeated, rows to remove |
How to fill it in
- Paste your list into List from A2. Put the column you want to deduplicate on — email, customer ID, phone — in column C.
- Sort the list first if it matters which copy is kept: the first occurrence from the top is marked Keep, so sort newest first to keep the latest record.
- Fill columns D to G down to the last row.
- Filter Action for Remove duplicate, review, and delete those rows (or copy the Keep rows to a new sheet).
How the formulas work
The clean key removes leading, trailing and doubled spaces. Matching is case-insensitive because COUNTIFS ignores case:
=TRIM(C2)
=COUNTIFS($D$2:$D$100,D2)The expanding range counts how many times the value has appeared so far, so only the first copy gets 1:
=COUNTIFS($D$2:D2,D2)
=IF(F2=1,"Keep","Remove duplicate")Worked example
Ten sample contacts. Anna appears once in capitals, Dev once with a trailing space and Ben twice with identical rows — all three are caught. In each case the first row from the top is kept and the later copy is marked for removal.
| Measure | Value |
|---|---|
| Total rows | 10 |
| Unique values | 7 |
| Values that appear more than once | 3 |
| Rows to remove | 3 |
Limits and when to move on
- TRIM does not remove non-breaking spaces from web exports; replace CHAR(160) first if matches are missed. Trailing and non-breaking spaces explains why.
- Only exact keys match. 'Jon Smith' and 'John Smith' are different values; for near-matches see matching names spelled differently.
- To check duplicates across two separate lists, use the compare spreadsheets tool or load both into Analistable and ask which records appear in both.
Frequently asked questions
- Can I check duplicates on two columns, like name and date?
- Yes. Change the clean key to =TRIM(B2)&"|"&TRIM(C2) so it joins both values, and the rest of the formulas work unchanged.
- Will it remove duplicates for me?
- No, on purpose. It marks rows so you can review them; filter on Remove duplicate and delete when you are satisfied.
- Does it work in Google Sheets?
- Yes. TRIM, COUNTIFS, COUNTA and IF behave the same in Google Sheets.