Duplicate transactions after a bank import: why it happens and how to clean up

You imported a bank file and now some transactions appear twice. It's one of the most common clean-up jobs in QuickBooks and Xero, and it nearly always has one of a few causes. Fix the cause and it won't happen again.

Why duplicates happen

• The import overlaps with the bank feed. The feed already brought in the last few weeks, and the file you imported covers some of the same days.
• Two imports overlap. Last month's download ran to the 31st, and this month's starts on the 25th.
• The file has no transaction IDs, or different ones. OFX, QFX and QBO files give every transaction an ID (the FITID), and software uses it to recognise a transaction it has already imported. A CSV has no IDs, and a bank or converter that makes up new IDs on each download defeats the check.

Cleaning up in QuickBooks Online

If the duplicates are still waiting in the For review tab of Bank transactions, select them and choose Exclude. They stay out of your books.

If they were already added or matched, find them in the Categorized tab and choose Undo. That sends them back to For review, where you can exclude them.

Cleaning up in Xero

Run Xero's Duplicate Statement Lines report for the bank account. It lists statement lines that look like duplicates so you can review and remove them. Check each one before deleting it: two genuine purchases of the same amount on the same day happen more often than you'd think.

Stopping it happening again

1. Before every import, look at the date of the newest transaction already in that account, from the feed or an earlier import.
2. Download, or trim, the file so it starts the day after. In a CSV, delete the earlier rows. With OFX, choose the date range when you download.
3. Prefer OFX over CSV where you can. The IDs give the software a second chance to catch an overlap you missed.

If an OFX file has missing or repeated IDs, the free OFX and QFX viewer counts them. Its repaired file gives every transaction a unique ID while keeping the original wherever it was already unique, so transactions you imported before are still recognised.

Check the totals afterwards

Once the account is cleaned up, compare its balance in QuickBooks or Xero with the bank statement's closing balance for the same date. If they agree, the duplicates are gone and nothing real was deleted along with them. If you're working from PDF statements, the statement converter checks each statement against its own balances before you import anything.