Build a key for each row from the date, amount, balance and narration, add an occurrence number so that genuine repeats inside one file stay distinct, flag the rows whose key appears again, and only then delete. Using Remove Duplicates on date and amount alone would erase real repeat payments, such as two identical bus fares in one day.
Why does overlap create duplicates?
Statements are cut by dates you choose, and it is easy to download 1 April to 7 May and then 1 May to 31 May. The week from 1 to 7 May now appears twice. Totals double-count that week, and every report built on it is wrong in a way that still looks plausible.
How do you build a safe key?
Assume columns A date, B narration, C debit, D credit, E balance, F source file name. In G2 enter:
=TEXT(A2,"yyyy-mm-dd")&"|"&B2&"|"&C2&"|"&D2&"|"&E2
If the statement carries a running balance, the key nearly identifies a transaction by itself, since two genuine identical payments leave different balances. If your files have no balance column, drop it, and the next step becomes essential.
TEXT(A2,"yyyy-mm-dd") needs a real date in A2. If your dates are text, convert them first, or at least be sure both files write the date the same way, because a key built from unlike text will never match.
What is the occurrence number for?
In H2 enter =COUNTIFS($F$2:F2,F2,$G$2:G2,G2) and fill down. It counts how many times this key has appeared so far within the same file. Then in I2 enter =G2&"#"&H2. Two identical bus fares on one day in the same file become key#1 and key#2, so both survive. The overlap week, present in both files, produces the same key#1 in each, so the second copy is a true duplicate.
| Row | File | Key | Occurrence | Final key |
|---|---|---|---|---|
| Bus fare, 3 May, ₹30 | April-May | K1 | 1 | K1#1 |
| Bus fare, 3 May, ₹30 | April-May | K1 | 2 | K1#2 |
| Bus fare, 3 May, ₹30 | May | K1 | 1 | K1#1 |
| Bus fare, 3 May, ₹30 | May | K1 | 2 | K1#2 |
In this invented test, the four rows reduce to two once you remove repeated final keys, which is right: two real payments, each listed twice.
How do you flag before you delete?
- In J2 enter =IF(COUNTIF($I$2:I2,I2)>1,"Duplicate","Keep") and fill down. The first occurrence of each final key is kept, later ones flagged.
- Filter column J for Duplicate and read through the rows. They should all sit in the overlapping dates.
- Delete those rows, or copy the Keep rows to a new sheet. If you prefer the Data, Remove Duplicates button, tick only the Final key column; it removes the full row for each repeated key.
What about narration that differs slightly?
The same transaction can be worded differently in two downloads, with extra spaces or a changed reference. Wrap the narration in TRIM, and build the key without long reference numbers if your bank changes them. If a duplicate survives, do not force the key; review such rows by hand.
Keep key length in mind too: COUNTIF and COUNTIFS cannot match text strings longer than 255 characters. Narrations are normally shorter, but a long reference plus other columns might not be. If results look odd, shorten the key by using only the first 60 characters of the narration with LEFT(B2,60).
Before you finish
- Keep the original files and the flagged sheet.
- Compare totals before and after: the difference should equal the flagged rows' amounts.
- Re-run your balance continuity check across months.
SpendixAI flags possible duplicate imports and has a report that lists same-amount charges to the same merchant within two days, which helps when you have uploaded overlapping statements.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


