Spreadsheets

Remove duplicate rows after merging two statements in a sheet

Remove duplicate rows after merging two bank statements in a sheet: build a key from date, amount, narration and balance, then use Remove Duplicates or UNIQUE.

Editorial illustration for Remove duplicate rows after merging two statements in a sheet

When two statement downloads overlap in date, the overlapping days appear twice after you paste them together. To remove those duplicates safely, build a key column from date, amount, narration and balance, then use Data, Remove Duplicates on that key, or the UNIQUE function in Excel 365 and Google Sheets. Including the balance is what protects genuine repeat payments from being deleted.

Why do duplicates appear in the first place?

Say you download one statement for 1 April to 15 October and another for 1 October to 31 March, because you were not sure where the first ended. The days from 1 to 15 October now exist in both files. Pasted into one sheet, every transaction in that window shows twice, and totals for those weeks are doubled.

How do you build the key column?

Assume the merged sheet has Date in A, Narration in B, Debit in C, Credit in D and Balance in E, rows 2 to 1000. In F2 enter:

=TEXT(A2,"yyyymmdd") & "|" & TRIM(B2) & "|" & C2 & "|" & D2 & "|" & E2

Fill it down. Each row now has a single text string that joins the fields. Two rows are true duplicates only when every part matches, including the balance after the transaction. Two coffees bought a minute apart share a date, narration and amount, but their running balances differ, so their keys differ.

How do you remove the repeats?

  1. Copy the whole tab as a backup before you start.
  2. Click anywhere in the data and choose Data, Remove Duplicates.
  3. Untick everything, then tick only the key column F. Tick My data has headers.
  4. Confirm. Excel reports how many duplicate values were removed and how many unique values remain. It keeps the first occurrence.
  5. Sort by date again if you need the chronological order.

If you have Excel 365 or Google Sheets, a cleaner option is =UNIQUE(A2:F1000) in a new tab. It returns the distinct rows and leaves your merged data alone.

How can you tell the clean-up worked?

Use arithmetic, not eyesight. Say file one has 212 rows, file two has 187 rows and you know 31 transactions fall in the overlap. The merged sheet should end with 212 plus 187 minus 31, which is 368 rows. If you get 380, some copies survived. If you get 350, you deleted real transactions, probably because you left the balance out of the key.

Then compare the closing balance in the last row with the closing balance printed on the statement for that date. If they match, the sequence is complete.

CheckExpected resultIf it fails
Row count after clean-upRows in file one plus file two minus overlapRebuild the key and compare again
Last balance in the sheetEquals the statement closing balanceLook for a missing or deleted row
Total debits for the overlap daysCounted once onlyFilter the overlap dates and inspect

What if the narrations differ between the two downloads?

Sometimes the same transaction is worded slightly differently or has an extra space. Those rows will not match on the full key. In that case, drop the narration from the key and use date, debit, credit and balance only. The balance is so specific that it rarely produces false matches. Keep a note of the key you used.

Where can the dates go wrong?

  • Dates that arrive as text, such as 03/10/2026, may flip between day-first and month-first once pasted. Convert them to real dates before you build the key.
  • Amounts with commas stored as text will not add up, and may differ in the key from a real number.
  • Hidden rows stay in the data, so unhide everything before you count rows.

SpendixAI flags possible duplicate imports when you upload statements and lets you review rows before saving, which is the same overlap problem handled at the point of entry. For a one-off merge, though, the key column above does the job.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports