Two-File Reconciliation
Reconcile two sheets on a key, not on row order. Column order, column names and messy keys don't matter. Free up to 1,000 rows a side. Two-File Reconciliation is an editor add-on for Google Sheets™, published by AppGarden.
Install: Two-File Reconciliation on the Google Workspace Marketplace, or in Google Sheets™ open Extensions › Add-ons › Get add-ons and search for “Two-File Reconciliation”. It is an editor add-on for Google Sheets™ on desktop browsers; it does not run in the Sheets mobile apps. Already using Excel? The Excel add-in takes the same key.
New here? The step-by-step guide runs the whole thing on a worked example.
What it does
Put the two tables in one spreadsheet, or load the second one as a CSV. Then Extensions › Two-File Reconciliation › Open. The sidebar has three steps:
- The two tables. Pick each side from the tabs, the named ranges or the range you have selected.
- How the columns line up. The sidebar suggests a mapping from the column names and contents. Every line is marked as a guess until you change it. Set at least one key: the column that identifies the same thing in both tables.
- Reconcile, then write the report as new tabs.
Order and names don't matter
Map Invoice to Doc No once. The two tables can then be in any order, with any extra columns.
Messy keys still match
INV-0012, inv 12 and INV12 are one invoice. So are the text "0012" and the number 12. The report says which rows needed that.
Nothing leaves Google
The matching runs in the sidebar in your browser. Your spreadsheet content is never sent anywhere.
A guess is never a clean match
The add-on can also match keys that are only nearly the same. That is off by default, because a wrong near-match puts a tick against a row that never reconciled. When it is on, those rows are counted separately and never folded into the clean totals. Each one is listed on the Review tab with both keys quoted and an Accept? checkbox. Tick the ones you agree with, press Apply review and reconcile again. Those pairs now match, labelled as pairs you accepted. Save the recipe (Pro) and they match that way every month. Rows added together and keys that appear twice are also reported, never absorbed.
What you get back
Six new tabs, stamped with the date. Your source tabs are never modified.
- Summary: the four buckets, and the difference between the two tables split into what explains it. The split covers rows only on the left, rows only on the right, value differences on matched rows and rounding within tolerance. Once all of that is accounted for, "unexplained" should read 0.00.
- Differences: matched rows that disagree, biggest first.
- Left only and Right only: the full source rows.
- Review: everything the run had to assume, with the checkboxes.
- Audit: the recipe, both sources, row counts, the time, the version and every normalisation step that was on. It answers "how did you produce this" when you are not in the room.
It remembers
Save the mapping and settings as a recipe (Pro). Recipes are kept against your Google account, not the file, so next month's new spreadsheet finds last month's recipe: Extensions › Two-File Reconciliation › Run a saved recipe…. Save one into the spreadsheet as well and a colleague can run it too, free, composite key and near-matching included. You can also export a recipe as a file and import it into the Excel add-in, or bring one the other way.
Free and Pro
Free
£0 no account, no expiry
- Up to 1,000 rows a side, one pair at a time
- A single key column
- Exact, tolerance, sign-flip and date rules
- The full result on screen, and the report written to tabs
- Load and run a recipe someone shared with you, Pro keys and all
Pro
£12 a month, or £99 a year
- No row limit, up to Google's own spreadsheet size limit
- Composite keys: match on two or more columns at once
- Near-matching keys, reported separately and never counted as clean
- Save recipes, so next month is one click
- Download the whole report as CSV
- One key, Excel and Google Sheets™: 3 activations shared between them
Your data
The add-on asks for two permissions. It can see and change only the spreadsheet you open it in, not your Drive and not your other files. It can also show its sidebar. It reads the tables you pick and writes the report as new tabs. The only network traffic it makes is to the licence server, and only a licence key, an anonymous install id and the word "sheets" cross it. Privacy notice.
FAQ
Does it work on my phone? No. Editor add-ons run in Google Sheets™ in a desktop browser (Chrome™, Edge, Firefox or Safari), not in the Sheets mobile apps.
Gmail™ or Workspace? Both. On a Workspace account, your admin may have to allow the add-on first. If Get add-ons says it is blocked, that is the reason.
My second table is in another spreadsheet. Right-click its tab, Copy to › Existing spreadsheet, and pick this one. That also leaves the original file untouched as evidence. Or download it as CSV and load it into the sidebar, where it is read in your browser and uploaded nowhere.
How big can the tables be? Free reads the first 1,000 rows a side. Pro has no row limit of its own. Google caps a spreadsheet at 10 million cells, report included, and the add-on checks the report fits before writing it. If it would not fit, it says so and offers the CSV instead. The tested ceiling will be published here after the stress tests.
Dates: 06/03 or 03/06? You choose day/month or month/day in Matching options; the add-on never guesses. If you imported a CSV with Convert text to numbers, dates and formulas ticked, Sheets has already read the dates in the spreadsheet's locale. Check them before you reconcile.
Already bought it for Excel? Paste the same key into the sidebar. It takes one of your 3 activations. Nothing else to buy. Your recipes come across too: moving from Excel.
Doing it by hand? How to reconcile two Google Sheets™ with different column order, and why VLOOKUP is not matching. Reconciling something specific? A bank statement against a ledger, a CSV export against a sheet, or amounts booked as negatives on one side.
Support
Email support@appgarden.co.uk. Say which host (Excel or Google Sheets™) and what the sidebar showed. If a reconciliation came out wrong, a copy of the two tables is the most useful thing you can send, but only send data you are happy to share, and read the privacy notice first. Replies are best-effort, usually within a few working days.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Two-File Reconciliation is not affiliated with or endorsed by Google.