Reconcile two lists
Give it two lists that should agree. It matches them and builds a reconciliation sheet with formulas, with control totals proving the difference is fully explained.
Runs best in: Copilot in Excel, on the open workbook. Use Cowork when you want it run across several files or chained with other skills.
- Works in:
- Copilot in Excel, Copilot Cowork
- Chains with:
- Tidy this export cleans the inputs first. Unmatched invoices go to Aged debtors to action for chasing.
- Version:
- 1.2 (October 2026)
Who it's for
Bookkeepers, accounts seniors and managers who reconcile one list against another at month end. It suits anyone who spends time matching debtors to receipts or one system to another line by line.
The problem it solves
Two lists that should agree rarely do. Matching them by hand means scrolling between sheets, finding the part payments and the reference typos and then proving the difference is fully explained. It is slow, and a match forced on amount alone can hide the very error you were looking for.
What it does
What the skill does
Give it two lists and tell it which is being reconciled to which. It takes control totals from both before touching anything, then matches on reference with spaces, hyphens and case ignored, then on amount and payer, then on amount and date within a set window (60 days for invoices against receipts, 3 days when both lists record the same events) for the items left over, and says which pass matched each item. The result is a Reconciliation sheet built with formulas, showing what matched, what matched with a difference and what is only on one side, a summary block proving the difference is fully explained and an exceptions list with a notes column for you. Both source sheets are left untouched.
Use cases
- Reconciling the debtors ledger against receipts from the bank
- Checking the sales ledger against the bank at month end
- Comparing payroll against the PAYE submission
- Matching one system's export against another after a software change
- Finding part payments, reference typos and payments with no invoice
How to use it
Install it, then use it
Install House style first. It's the one file every skill reads for your firm name, sign-off, voice and format. Fill it in once and this skill's Your firm block only needs the settings for this job. Skip it and the skill asks for your firm name and sign-off once.
In Excel
- 1 Download the zip file from this page and unzip it. You'll have a folder called reconcile-two-lists with SKILL.md, README.md and an examples folder inside.
- 2 Open SKILL.md in any text editor and fill in the Your firm block at the top, such as your match order, date window, tolerances and column names.
- 3 In the Copilot pane, open Settings (...), then Manage skills > Custom skills > Create OneDrive folder > Open skills folder.
- 4 Put the reconcile-two-lists folder there, then select Refresh. The folder name must match the skill name, so don't rename it.
- 5 Give it the two lists, say which is List A and which is List B and ask it to reconcile them.
- 6 Check the summary block balances, then work through the exceptions list.
In Cowork
- 1 Download the zip file from this page and unzip it. You'll have a folder called reconcile-two-lists with SKILL.md, README.md and an examples folder inside.
- 2 Open SKILL.md in any text editor and fill in the Your firm block at the top, such as your match order, date window, tolerances and column names.
- 3 Open Customize > Skills, select the arrow next to Add, then Upload skill, and choose the zip (recommended) or the SKILL.md on its own. If you changed SKILL.md, upload that, or zip the folder's contents again first so SKILL.md stays at the top level.
- 4 Or skip the upload and put the reconcile-two-lists folder in your OneDrive at Documents/Cowork/skills/. Keep the folder name as it is.
- 5 Start a new Cowork conversation so it picks up the skill.
- 6 Give it the two lists, say which is List A and which is List B and ask it to reconcile them.
- 7 Check the summary block balances, then work through the exceptions list.
The skill itself
Read the full SKILL.md
This is the whole skill. Your firm name, sign-off and voice come from House style. Change the settings under Your firm for anything specific to this job. Everything else follows them.
---
name: reconcile-two-lists
description: "Use when two lists should agree and you need to know where they do not: debtors against receipts, sales ledger against bank, payroll against the PAYE submission, one system against another. Matches records, lists differences and unmatched items, and shows control totals on both sides."
---
# Reconcile two lists
Two lists that should agree rarely do. This skill matches them, tells you exactly what matched, what matched with a difference and what is only on one side, and shows totals on both sides so you can see the reconciliation at a glance. Formulas throughout, nothing typed in.
## Your firm
Shared settings (firm name, sign-off, voice, how dates and money are written) come from the `house-style` skill in this pack. Fill that in once and every skill uses it. If it is not loaded, ask for the firm name and sign-off once at the start and carry on.
Settings specific to this skill. Edit before first use. If a setting is blank but has a default, use the default and say so in one line. If a blank setting has no default and the job cannot be done without it, ask for all such settings together in one message at the start. If the job can be done without it, leave it out and say so. Never make up a value for a firm setting.
- Match order (default): reference first, compared with spaces, hyphens and other punctuation removed and case ignored, so "INV1046" matches "INV-1046"; then exact amount plus payer or customer name (case and punctuation ignored); then exact amount plus date within the window below. Say which pass matched each item.
- Date window for amount-and-date matching: default 60 days when matching invoices to receipts, because receipts follow invoices by weeks. Use 3 days when both lists record the same events, such as a sales ledger against the bank for the same period.
- Amount tolerance for "Matched, difference": default £0.01
- Output column names, if the firm has a convention:
## What you need from the user
Two lists, each as an Excel sheet, a CSV or a pasted table. For each list, at least: an identifier or reference, an amount, and ideally a date and a description. Tell the skill which is List A (the one being reconciled) and List B (the one being reconciled to). If not stated, A is the first file given.
If the lists have no shared reference, say so and ask which fields to match on. Do not match on description alone without saying it is weak.
## Method
If either list is a raw export, run Tidy this export on it first. Xero and FreeAgent exports put receipts in a signed or Received column, Sage uses Debit and Credit, and QuickBooks indents names; the known export quirks table in Tidy this export covers each. Reconciliation on untidied files produces false mismatches.
1. **Describe both lists** in two lines each: rows, columns, the field you will use as the key, the amount column, the date column.
2. **Control totals first**, from the raw data, using formulas: row count and sum of amounts for A, same for B. Write them down. The difference between the two sums is the amount the reconciliation must explain.
3. **Build a working sheet** called "Reconciliation". Never overwrite the sources. Columns: Key | A amount | A date | A description | B amount | B date | B description | Difference | Status | Notes. Add a normalised key column on each side (a formula that removes spaces, hyphens and punctuation and upper-cases the reference) and use XLOOKUP or equivalent on that key to pull B against A, with a second pass for B items with no match in A. Difference = A amount minus B amount, as a formula. Status = formula: "Matched" if difference within tolerance, "Matched, difference" if a match exists but amounts differ, "A only" and "B only" for unmatched. Where the raw references differ but the normalised keys match, note the difference (for example a missing hyphen) in the Notes column.
4. **Second-pass matching** for anything still unmatched, in this order: (a) exact amount plus payer or customer name, compared ignoring case and punctuation; (b) exact amount plus date within the window. Mark these "Matched (amount and payer)" or "Matched (amount and date)" so the user knows the reference did not line up, and note the reference difference. Never auto-match on amount alone if more than one candidate exists; list the candidates and leave it unmatched.
5. **Summary block** with formulas: count and sum for each status, and the check: sum of A equals (matched A amounts + A only), sum of B equals (matched B amounts + B only), and the explained difference equals the control total difference. Show Before | After | Difference.
6. **Exceptions list**: every row whose status is not plain "Matched", plus any matched row with a note (for example a reference that differed before normalising), sorted by absolute difference, largest first, with a blank Notes column for the user.
## Output
1. **The Excel file** with both source sheets untouched, the "Reconciliation" sheet with formulas, the summary block and the exceptions list.
2. **A short report**: control totals for both lists, how many matched, how many with differences, how many unmatched each side, the total of the unmatched items, and whether the reconciliation fully explains the difference between the two list totals. Anything that needs a human decision listed as a question.
## Guardrails
- Formulas only. No typed results.
- Never force a match. If it is ambiguous, leave it unmatched and say why.
- Never adjust an amount on either side.
- If the summary check does not balance, say so first and show where.
- British English. £1,234.56. Questions
Questions about this skill
Is the skill free?
Does it send anything?
Will it force a match?
How do I know the reconciliation is right?
Is there a sample to try?
Firmwide
Want your own firm's procedures built in and kept working? That's a Firmwide install.
A fixed-price install, typically six to eight weeks: Copilot made safe and useful in your Microsoft 365, an agent and a skills pack for every department, handed over with training. Then Run keeps it working.
Next step
Want this tailored to your firm?
A free 30-minute Copilot Readiness Check with James, online. You leave knowing where Copilot can and cannot see your data, the two jobs to automate first and a rough install price. Your own skills, built from your procedures and handed over to each department on its own work.
Related skills
All skills- Excel Has example + expected result Works for any team
Tidy this export
Turns a messy bank or practice software export into a clean Excel table on a new sheet, then proves nothing was lost with row counts and totals before and after.
Includes a known export quirks table for Xero, Sage, QuickBooks Online, FreeAgent, bank CSVs and practice software.
- Job
- Getting data in shape
- Works in
- Copilot in Excel, Copilot Cowork
- Chains with
- Run it first on any export, then hand the clean sheet to Bank statement summary, Reconcile two lists, Aged debtors to action, Variance hunter or Year-on-year comparison.
Version 1.2 Download Tidy this export - Excel Has example + expected result Works for any team
Variance hunter
Builds a variance table with formulas, flags the movements over your threshold, labels them favourable or adverse and drafts the commentary.
- Job
- Month-end and reporting
- Works in
- Copilot in Excel, Copilot Cowork
- Chains with
- Tidy this export and Spreadsheet health check prepare the source. The variance sheet feeds Numbers to board slides. Commentary uses House style.
Version 1.2 Download Variance hunter - PowerPoint
Numbers to board slides
Turns management accounts into a short board or client deck without anyone retyping a number.
- Job
- Month-end and reporting
- Works in
- Copilot in PowerPoint, Copilot Cowork
- Chains with
- Takes the output of Variance hunter directly. Uses House style for commentary and firm name.
Version 1.2 Download Numbers to board slides