Price List Reconciler

Supplier Price Lists vs the ERP: A Tiered Matching Engine in Excel and Access

25 September 2026 by

Client: International Salon Supplies

Role: Developer (VBA / SQL)

Period: Jul 2023 – Nov 2023

Status: Used in production by the purchasing team

Tech stack: Excel VBA, Microsoft Access, SQL, ADO, Retail Express

Suppliers send price lists with inconsistent codes, barcodes and naming, and every update had to be matched against thousands of ERP products by hand. I built a reconciler that uses Access as a SQL engine behind an Excel front end: a cascade of match tiers, a manual-matching sheet for the leftovers, GST-aware margin checks, and a ready-to-upload file for the ERP.

The problem

When a supplier changes prices, ISS receives a spreadsheet. The ERP holds its own view of the same products. Matching the two sounds simple until you meet real data:

Some rows match on SKU ↔︎ barcode, others only on a secondary supplier code.
Some keys appear more than once on one side (one-to-many), and some on both sides (many-to-many).
Sizes, colours and carton quantities don't always agree.

Doing this by eye across thousands of rows took days and invited mistakes that went straight into pricing.

Approach: SQL first, then a tool

I prototyped the logic in plain SQL: CTEs that sort two-key matches into four buckets (1:1, 1:many, many:1, many:many) using GROUP BY … HAVING COUNT. Once the rules were right, I wrapped them in a tool that non-developers could run.

Architecture

Supplier price list ──┐
                      ├─► Excel front end (VBA) ──► Access database (SQL engine via ADO)
ERP product export  ──┘                                   │
                                                          ▼
                              Match cascade (each tier claims rows, sets Resolved = true)
                                                          │
                     Working sheet (live formulas) · Manual-match sheet · Upload template

Each release shipped as a small package: an Access database, a loader workbook, a supplier template and an upload template. The purchasing team could go from import to an ERP-ready upload file without leaving Excel.

The matching cascade

Two keys are compared: SKU ↔︎ barcode and secondary SKU ↔︎ supplier code. The tool counts how often each key appears on each side, then runs tiers from most to least certain:

Perfect pair match: both keys unique on both sides
Single-key matches where only one key is reliable
1:many and many:many groups for each key and for the key pair
Loose matches
No match

Every row has a Resolved flag, so later tiers only consider rows that earlier, more confident tiers didn't claim. Whatever's left lands on a manual-match sheet with Go-to, Match and Unmatch buttons, so a person settles the genuinely ambiguous cases.

The working sheet

Instead of pasting values, the tool writes live formulas that reference the source sheets, so every number stays traceable. The sheet calculates:

Buy price match and difference
Gross profit including 10% GST, (sell − buy × 1.1) / sell, for current and proposed prices
Space for two competitor prices
Exact-match checks on size, colour and carton quantity

Iteration

The tool went through about ten numbered packages (V1 → V2, Package 1 → 4), each driven by feedback from the people using it every week. An earlier reconciler app (v1.3, July 2023) came before the Access-backed version.

By the numbers

~800 lines in the core matching class, ~2,200 lines of VBA across the reporting toolset
SQL prototype (Aug 2023) → production packages (Sep – Nov 2023)

Takeaway

The right tool isn't always a web app. The purchasing team lived in Excel, so the best solution was to bring a real SQL engine and a disciplined matching algorithm to where they already worked.

in Projects