How to combine supplier price lists into one spreadsheet
By the Analistable team · Updated · 5 min read
Map each supplier's product code to your own SKU, convert every price to the same unit and currency, then stack the lists with a Supplier column. A pivot or MINIFS on the combined table then shows each SKU's cheapest supplier and the price gap to the next one.
Part of our guide: How to join spreadsheets on a common column
Why stacking beats side-by-side lookups
With two suppliers you can put their prices side by side with XLOOKUP. With five, that becomes five lookup columns that break whenever a supplier changes their layout. Stacking every list into one long table — SKU, supplier, price, pack size, minimum order — scales to any number of suppliers and makes "cheapest per SKU" a single formula.
This page is about comparing several suppliers at one point in time. Comparing one supplier's old and new list to see what went up is covered in comparing two price lists.
Step 1: build a code map
Suppliers rarely use your SKU. Each has its own code, and the same product may come in different pack sizes. A small mapping table fixes both:
| Supplier | Supplier code | Our SKU | Units per pack |
|---|---|---|---|
| Northline | NL-A4-80 | PAP-A4 | 2500 |
| Brightwell | BW1102 | PAP-A4 | 500 |
| Officeco | A4WHT80 | PAP-A4 | 2500 |
| Northline | NL-TON-26 | TON-26A | 1 |
| Brightwell | BW7720 | TON-26A | 1 |
Join each supplier list to this map on Supplier and Supplier code. Rows that don't match are new lines or code changes; review them rather than dropping them silently. The guide to joining spreadsheets explains the join options in Excel, Sheets and Power Query.
Step 2: normalise and stack
- Load each supplier's list and keep only the code, description, price, currency and pack size columns.
- Add a Supplier column with a fixed value per list.
- Merge with the code map to bring in Our SKU and Units per pack.
- Add Unit price = Price ÷ Units per pack, converting currency at a stated rate if needed.
- Append all suppliers into one table (Power Query Append, or VSTACK in Microsoft 365).
Record the currency rate and the date of each list in the table. A comparison is only fair if prices are from the same period and on the same basis — ex VAT, same delivery terms.
Worked example: three suppliers
| Our SKU | Supplier | Pack price | Units per pack | Unit price |
|---|---|---|---|---|
| PAP-A4 | Northline | 21.25 | 2500 | 0.0085 |
| PAP-A4 | Brightwell | 4.5 | 500 | 0.009 |
| PAP-A4 | Officeco | 20 | 2500 | 0.008 |
| TON-26A | Northline | 64 | 1 | 64 |
| TON-26A | Brightwell | 59.5 | 1 | 59.5 |
Find the cheapest unit price per SKU, and who offers it:
=MINIFS(Prices[Unit price], Prices[Our SKU], A2)
=XLOOKUP(1, (Prices[Our SKU]=A2)*(Prices[Unit price]=B2), Prices[Supplier])| Our SKU | Best unit price | Supplier | Next best | Gap |
|---|---|---|---|---|
| PAP-A4 | 0.008 | Officeco | 0.0085 | 6.3% |
| TON-26A | 59.5 | Brightwell | 64 | 7.6% |
Brightwell's paper looks cheap at £4.50 a pack until it is converted to a unit price: £0.0090 a sheet, the dearest of the three. Without the pack-size column the comparison would point the wrong way.
Beyond the cheapest price
- Minimum order quantities and delivery charges can outweigh a small unit price gap; add them as columns and compare landed cost for your usual order size.
- Coverage: a pivot of SKU by Supplier with a count shows which products only one supplier carries — a supply risk.
- Spend impact: join last year's purchase volumes and multiply by the price gap to see what switching would save.
Analistable can hold the supplier lists, the code map and your purchase history together and answer "which SKUs would save most if we switched supplier" by writing the joins itself. It runs in your browser, so the price lists aren't uploaded, and it doesn't edit the files you give it.
Getting supplier lists into shape
Supplier price lists arrive in every format: PDFs, Excel files with logos and notes at the top, CSV exports from a portal. The combine step is easier if each list goes through the same small clean-up first:
- Remove the decoration: delete title rows, blank rows and footnotes so the header is in row 1.
- Split price and currency: a cell like "£21.25" stored as text should become the number 21.25 in a Price column with GBP in a Currency column.
- Check VAT: some trade lists are ex VAT, some retail ones include it. Put everything on one basis before comparing.
- Keep tier prices separate: if a list shows prices for 1–9, 10–49 and 50+ units, unpivot them into rows with a Min qty column rather than three price columns.
- Date the list: add a Valid from column so an old list doesn't win a comparison on stale prices.
In Power Query you can save these steps per supplier. When a new list arrives in the same layout, replace the file and refresh; only a change of layout needs the steps edited. For PDF lists, Excel's Get Data → From PDF (available in Microsoft 365 on Windows) can extract tables, though the result usually needs checking line by line.
Keeping the comparison current
A price comparison goes stale quickly. Keep one folder per supplier with the latest list, and keep superseded lists in an archive folder rather than deleting them; stacking the archive with a Valid from column gives you each supplier's price history per SKU. That history is useful in negotiations: you can show how often a supplier has raised prices and by how much, rather than relying on memory. Review the unmatched-code list each time a list is refreshed, because suppliers renumber products more often than they announce it.
Frequently asked questions
- How do I compare prices when suppliers sell in different pack sizes?
- Convert everything to a price per unit (or per kilo, per metre) using a pack-size column from your code map, then compare unit prices rather than pack prices.
- What if two suppliers use the same product code for different items?
- Always join on Supplier and Supplier code together, never on the code alone. The code map should have one row per supplier and code pair.
- Can I keep updating the comparison as new price lists arrive?
- Yes. If you build it in Power Query, replace a supplier's file with the new list and refresh; the code map, unit prices and cheapest-supplier columns update. Check the unmatched codes after each refresh.
- How do I handle tiered or volume prices?
- Unpivot the tier columns into rows with a minimum quantity, then compare suppliers at the quantity you normally order. A supplier that is cheapest at 50+ units may not be cheapest for a typical order of ten.