Tired of Excel reconciliations that say MATCHED but hide duplicates and missing entries underneath — here's a framework that catches them

After 10 years in FP&A dealing with ERP reconciliations, group audit submissions, and month-end closings across multiple companies and systems, I got frustrated enough with standard Excel reconciliation approaches that I developed my own framework. I'm calling it **Vertical Netting**.

I'm sharing it because I genuinely searched for something like this before building it — and found nothing. If it exists somewhere publicly I haven't seen it.

---

## The Problem With Standard Methods

Most reconciliation tutorials teach you to put two datasets side by side and compare them — VLOOKUP, MATCH-INDEX, conditional formatting, or the pivot table's "Difference From" setting.

These approaches have a specific failure mode that rarely gets discussed:

**They can show MATCHED when the reconciliation is not actually clean.**

If Source A has a duplicate entry and Source B is missing an entry, and the amounts happen to be equal — the totals agree. The reconciliation shows green.
But the underlying transactions are wrong.

Real example: payroll reconciliation. GL total = Bank total = $85,700.
Standard check passes. But GL has Employee E003 posted twice (duplicate) and Employee E007's bank transfer failed silently. The duplicate ($5,200) offsets the missing payment ($5,200). Totals agree. E007 never received their salary.

Standard VLOOKUP: MATCHED.
This method: flags both immediately.

---

## The Core Insight — Reframe The Problem

Standard methods treat reconciliation as a **comparison problem**: put two things side by side, measure the gap.

This framework treats it as a **summation problem**.

The insight comes from double-entry bookkeeping — the foundational principle accounting has used since 1494. Every transaction recorded twice with opposite signs. The system self-checks by arithmetic. The books either balance or they don't.

Applied to Excel reconciliation:

- Source A (current / GL / new version) → **Multiplier = +1**
- Source B (prior / Bank / old version) → **Multiplier = −1**
- Stack both datasets into one combined sheet
- Add a Net Amount column = Original Amount × Multiplier
- Run a pivot table

**If everything matches → Grand Total = 0. Automatically.**

No "Difference From" configuration. No base field to misconfigure. No silent wrong answers. Just arithmetic doing what arithmetic does.

---

## The 5-Step Framework (Vertical Netting)

**Step 1 — Identify your two sources**
Assign +1 to the more current/authoritative source.
Assign −1 to the baseline/prior source.

**Step 2 — Load data into Version_A (+1) and Version_B (−1) sheets**
Paste or connect your data. Headers can be different between sources — the Mapping Table handles the translation.

**Step 3 — Configure the Mapping Table**
Four columns: Unified_Header | Source_A_Header | Source_B_Header | Numerical_Y_N
This is where schema mismatches get resolved. GL calls it "Posting_Date", bank calls it "Value_Date" — map them to the same unified name here.
Mark amount/quantity fields with Y — Power Query applies the multiplier to these.

**Step 4 — Refresh Power Query**
PQ stacks both sources, assigns Version labels and multipliers, calculates Net = Amount × Multiplier for all marked fields.
One refresh. Everything updates.

**Step 5 — Read the pivot**

Net Amount	Multiplier Sum	Meaning
= 0	= 0	✓ Matched — clean
≠ 0	= 0	⚠ Amount changed
≠ 0	= +1	⚠ Only in A — new/added
≠ 0	= −1	⚠ Only in B — removed/settled
= 0	≠ 0	⚠ COUNT MISMATCH ← this is the one standard methods miss

The last row is the key insight. Net amount looks like a match. But the transaction count doesn't agree. Duplicate in one source offsetting a missing entry in the other. Standard methods: MATCHED. This method: INVESTIGATE.

---

## Why Pivot Tables Are The Right Tool For This

Pivot tables are **addition engines**. They were designed to sum.

The "Difference From" setting forces them to subtract — working against their natural design, with silent misconfiguration risk if you set the wrong base field.

By converting the comparison into a summation problem through the multiplier, you work *with* the pivot's natural strength. Nothing to configure. Nothing to misconfigure.

---

## 5 Real-World Applications In The Template

**1. Bank Statement vs GL**
Classic month-end bank rec. GL has 16 columns, bank has 10 with different names.
Mapping Table normalises both. Missing entries flag immediately with their reason (credit card statement not forwarded to FNA, payment gateway timing lag).

**2. Weekly Sales/GP Variance**
This week vs last week. 1015 vs 1000 rows.
Actuals (shipped orders) never change — they always net to zero, confirming data integrity. Only projections change. New programs, removed forecasts, quantity revisions all surface automatically. Unit FOB stays constant when qty changes — so a GP% change signals a pricing revision, not just volume.

**3. AR/AP Settlement Tracking**
Compare SAP open balance downloads at two dates (FBL5N Key Date = Dec-31 and Mar-31). No payment matching needed. The disappearance of an invoice from the open balance IS the settlement signal. Settled, partially settled, still outstanding, newly raised — all classified automatically.

This is the same snapshot-comparison approach that enterprise AR reconciliation platforms use as their first-pass classification layer. The difference is those platforms then automate the document-level lookup for remaining items.
For Excel-based teams, this method handles the majority of the classification work using data you already know how to pull from SAP.

**4. GL vs Trial Balance (ERP Migration)**
Validate that your offline GL agrees to the ERP Trial Balance after upload.
Both should net to zero independently (Dr=Cr). Non-zero rows by CoA = batch not yet reflected in TB. Catches upload timing gaps before they become audit issues.

**5. GL Version Control**
New GL file received from accounting firm after you already uploaded the old one.
Both versions balance internally (net=0). Differences surface at document level: changed amounts, removed entries, new entries. Tells you exactly what needs re-uploading and whether it's localised or widespread.

---

## What's In The Download

**Master Dataset File** (20 sheets):
- Scenario overview with all 4 fundamental scenarios illustrated
- Simple 10-row demo showing every scenario in plain English
- Payroll count mismatch demo (the case standard methods miss)
- 5 datasets with realistic sample data for each application above
- Notes sheet for each dataset explaining the PQ setup

**5 Power Query Template Files** (one per application):
Each file has 5 sheets:
- Version_A (+1) — paste your first source here
- Version_B (−1) — paste your second source here
- Mapping Table — configure field mappings and mark numerical fields
- Invoked Function — PQ output, auto-generated, do not edit
- Pivot Table — pre-configured detection engine with OK/Please Check classification

The Mapping Table is the key architectural feature. It means you can adapt any template to your own schema without touching PQ code — just update the field mapping and refresh.

---

## Limitations — Being Honest

- Built for Excel-based teams. Not a substitute for enterprise reconciliation software (BlackLine etc) which adds automated document matching, ERP direct integration, workflow controls, and SOX compliance governance.
- The Power Query version requires Excel with PQ (2016+). A formula-based version is possible but less flexible.
- For complex AR scenarios (partial payments across multiple invoices, credit note netting, FX revaluations) — the method surfaces the variance and tells you where to look, but the resolution still requires manual investigation. This is true of any Excel-based approach.
- The Grand Total = 0 signal can be misleading for GL version control (both versions balance internally so total always = 0 — look at row level instead).

---

## Files

[Link to download — master dataset + 5 PQ templates]
https://drive.google.com/drive/folders/1TPO67MFfxFWvFk73q0aD9Xq4PhkLpMjh?usp=drive_link