Reconcile two tables

How to reconcile two tables

1. Drop both tables. One side is usually a statement you received (bank, supplier, marketplace payout); the other is what you recorded. Both stay in your browser.
2. Confirm the reference and amount columns. They are guessed from the header names — invoice, reference, cheque, amount — and can be changed from the dropdowns.
3. Set the tolerance. A cent or two covers rounding; a larger amount covers bank fees taken out of a payment. Anything above it is listed as a mismatch, not hidden.
4. Work the two leftover lists. "Only in the first table" and "only in the second table" are the rows to chase. Download the report as .xlsx with one sheet per category, or as one CSV.

Why the VLOOKUP version keeps lying to you

The usual way to reconcile in Excel is a VLOOKUP from one sheet into the other, then a filter on #N/A. It answers one question — "is this reference over there" — and quietly gets three things wrong. It matches the first row it finds and ignores any repeat, so a payment that was split in two comes back as a single match with the wrong amount. It compares text to text, so 00012345 in the statement and 12345 in the ledger never meet. And it has no idea whether the amounts agree; that is a second formula, and a third for the reverse direction.

This page does the four passes at once: pairs by reference (with the zeros folded, and full-width digits treated as digits), compares amounts on the pairs it found, and lists what is left on each side. The "repeated references" switch decides whether a second row with the same reference is added to the first — instalments, part-payments — or listed as an extra that needs a look.

The four lists, and what each one usually means

• Matched. Same reference on both sides, amounts within tolerance. Nothing to do; the report keeps them so the totals add up.
• Amount mismatches. Same reference, amounts further apart than your tolerance. Bank fees deducted from a transfer, a short payment, a typo in the ledger. The difference is shown per row and totalled.
• Only in the first table. On the statement, not in your records: a payment you have not booked yet, a customer who paid twice, a direct debit nobody entered.
• Only in the second table. In your records, not on the statement: an invoice still unpaid, a cheque not yet cleared, or a payment that arrived under a different reference — that last one is the case for fuzzy matching on the description column.

Rows with an empty reference are set aside and counted rather than silently matched to each other; an empty key matching an empty key is how reconciliations come out "clean" and wrong.

Tolerance is a decision, not a default

A one-cent tolerance catches rounding between systems that store two decimals and systems that store four. Setting it to the size of a typical card fee lets those pairs count as matched, which is convenient and also hides the fees — so the report shows the total difference across mismatched rows separately, and you can run the same two files twice with different tolerances in a few seconds. What the page will not do is guess: a pair is either within the number you chose or it is listed.

Bank statements that arrive as PDF

If the statement is a PDF rather than a spreadsheet, run it through the bank statement converter first; it reads the text layer, rebuilds the columns and checks that the running balance adds up, then gives you a CSV to drop here. A scanned image needs OCR, which this site does not do in the browser.

Every month, not just this once

A one-off reconciliation is exactly what this page is for. Doing the same one every month — same bank export, same ledger, same three reasons rows fail to match — is the job the TableDI desktop app is built around: the reference and amount columns, the tolerance and the fixes you added last month are kept as a job, next month's files are dropped in, and the exceptions come back as a list with a reason next to each one.

Nothing leaves this tab

Bank statements and ledgers are the files least suited to a free website with an upload button. Both tables are parsed by JavaScript in your browser, the matching runs there, and the reports are built there; no request carrying your data appears in the network tab while you work.