Settings

The two lists

Where it landed

The exceptions

The output that matters: the short list a person has to look at, with the reason each one is on it.

How it matched

In passes, hardest evidence first, so a pairing found on a reference is never overwritten later by a coincidence of amount.

The report


  
How it works

The interesting rows are the ones that nearly match

Reconciliation is the same job whatever it is called: a bank statement against a ledger, a stock count against a system, an invoice list against a remittance, a payroll file against a rota. Two lists that ought to describe the same events, and the work is finding the ones that do not pair up.

Done by eye it takes hours and misses things. Done with a lookup formula it finds the exact matches and silently drops everything interesting on the floor, because the interesting cases are precisely the near misses. The right reference with the wrong amount. The right amount with a reference that has a space in it. A reference typed with an O instead of a zero. One payment covering three invoices. A refund. A duplicate.

A lookup returns #N/A for all of those, which looks identical to “not there”, and the ordinary response to a column of #N/A is to work down it by hand, which is where the hours went.

Matching in passes, hardest evidence first

So this matches in stages and reports what each stage consumed. Exact key first. Then the key with punctuation, spacing and case taken out. Then amount within a tolerance. Then the looser tests.

The order matters, and so does the reporting. Running the loose tests first would pair things by coincidence: two invoices for the same round number in the same month will match on amount and be wrong. Taking the strongest evidence first means every row is claimed by the best reason available, and a row that survives to a later pass is a row you should look at.

The counts are the check on your own assumptions. “Exact key: 412. Normalised key: 38. Amount within tolerance: 6. Unmatched: 11” tells you something no total can: that 38 references in your data are formatted inconsistently, which is a problem worth fixing upstream rather than reconciling around every month.

The unmatched list is the output

Everything that pairs is, by definition, not news. What you came for is the residue, and each kind of residue means something different:

  • In the bank, not in the ledger: a fee, a payment you were not told about, a customer who paid without reference, or money that is not yours.
  • In the ledger, not in the bank: timing, usually, and if it is still unmatched next month it is not timing.
  • Matched on amount but not on reference: almost always a data-entry difference, and occasionally two genuinely different transactions that happen to be the same size.
  • One against many: a consolidated payment, which is correct and which no pairwise match will ever resolve on its own.

Do it monthly. A discrepancy found within weeks is still a question somebody can answer; the same one found in January is a line nobody remembers and usually gets written off. Nothing you load is uploaded. Nearby: LEDGER is the cash book on one side of it, SPLIT cuts a CSV into the shape you need first, CLEANSHEET tidies the values, and MATCH handles the harder problem of matching names rather than references.

The guide: Why VLOOKUP doesn’t match values that look the same

Questions people ask about RECONCILE

Why match in passes?

Because evidence has strength. A pair that agrees on both the reference and the amount is almost certainly the same transaction; a pair that agrees only on the amount might be a coincidence. Matching strongest-first and removing each pair as it is found stops a weak coincidence consuming a row that had a strong match waiting.

What does one against several find?

A single payment covering more than one invoice, which is the most common reason a reconciliation does not balance and the one a lookup formula never finds. It looks for subsets summing to a single row on the other side, capped at a few rows because a sum found among thousands of subsets is a coincidence rather than a finding.

Why does it ignore punctuation in references?

Because INV-1001, inv 1001 and INV1001 are one invoice, and they arrive spelled all three ways from three systems. The pass that does this runs after the exact one, so an exact match is never lost to a looser interpretation.

Is my bank statement uploaded?

No. Both files are read in this tab and there is no network call on the page, which for a statement is not a small point.

Doing this often? OBSCURA Books imports bank statements and ticks the lines off against the books until the difference is nothing. Everything is US$5 a month or US$48 a year.