Overlapping bank exports: review duplicates without losing payments

Treat matching rows from overlapping exports as duplicate candidates, not automatic deletions. Two genuine payments can share a date, description and amount. First establish which account and date range each file covers, then compare transaction identifiers and source positions.

Inventory the files before comparing transactions

1. Add source-file and original-row columns before combining. Confirm account, currency and the date field used for each export boundary.

2. Create a candidate list in the overlap only. Compare full references, direction, amount and available balances; leave uncertain pairs unresolved.

3. Record the row retained and the reason for excluding its counterpart. Keep both raw files and an exclusion log so the decision can be reversed.

Fictional example

File A covers 1–31 October; file B covers 25 October–15 November. Both contain reference TX-A17, debit 32.00 on 27 October: the repeated reference is useful evidence. On 28 October, two separate 8.00 card payments have different references. A rule based only on date and amount would wrongly remove a real payment.

Define the overlap before matching rows

Write a small inventory: A uses booking dates from 1 to 31 October; B uses the same field from 25 October to 15 November, for the same account and currency. The review window is therefore 25–31 October inclusive in this fictional case. If one export instead uses value dates, that window is not yet comparable. Resolve the date meaning first; extending a match to the entire combined table creates unnecessary candidates.

A repeated reference is strongest when its other fields agree. If TX-A17 has 32.00 in A and 23.00 in B, do not keep whichever version appears first: inspect both sources. If the bank offers one native export for the required full period, it may avoid stitching files; still verify its boundaries and fields.

Make exclusions explainable

Assign each raw row a stable key, such as A:17 or B:4, and give reviewed pairs a status: confirmed duplicate, distinct transactions or unresolved. Store retained key, excluded key and evidence together. In a small fictional subset containing two copies of the 32.00 payment and the two distinct 8.00 payments, raw debits total 80.00 over four rows. Excluding only the confirmed repeated 32.00 leaves three rows and 48.00. Those changes must agree with the exclusion log.

Select the retained source key explicitly after review instead of letting row order decide which observation survives. Explicitly select the reviewed source key. Keep both references available through the log even when only one payment enters the final table.

Review the evidence before choosing a retained row

A reference field can contain a merchant or batch code rather than a unique transaction ID. Establish its meaning from the bank’s export description where available. Keep the complete raw reference; a shortened or normalized copy is only a search aid.

Observation in the same account and currency	Decision	Evidence still needed
Same transaction ID, amount, direction and booking date in two exports	Candidate for one retained movement	Confirm the ID’s meaning and both source rows
Same date, description and amount only	Keep unresolved	Distinguish two payments from two observations of one payment
Same reference, different amounts	Do not merge	Check source transcription and transaction details
Debit and matching credit	Preserve both movements	Determine whether they form a reversal; they are not identical copies
Three verified copies of one booking	Retain one source key, exclude two	One group decision linked to all three sources

With three overlapping files, review groups rather than independently deleting pairs. In another fictional case, A:17, B:4 and C:2 all document the same EUR 32.00 debit. Their raw total is 96.00; retaining A:17 excludes 64.00 across exactly two rows and leaves 32.00. A pair log containing A/B, A/C and B/C must not produce three exclusions. List each excluded source key once and name its retained counterpart.

Build a candidate list from the selected comparison fields on a review copy, preserving source locators beside each candidate. Do that on a review copy; the resulting list is not the complete export. Source-file and source-row fields must stay available but must not be part of a key intended to match across files. Preserve unmatched rows and unresolved groups in separate review views, then reconcile every raw row to either the retained table or the exclusion log.

Checks and limits

Check counts and separate debit/credit totals before and after exclusions, especially on the first and last overlapping dates. Power Query’s duplicate removal depends on selected comparison columns; it does not establish transaction identity. If no reliable identifier exists, compare the source visually and retain ambiguous pairs for review.

Related checks

Confirm periods and references before removing a suspected duplicate. Booking date and value date: keep both in a bank statement export · Bank references in Excel: protect long IDs and leading zeros · Check a bank statement balance without hiding extraction errors.

BankStatementScan is in preparation. The site offers an access request and a fictional example; it does not currently convert your bank files. See the planned service.

Sources consulted on 16 October 2026

• W3C — Model for Tabular Data and Metadata on the Web.