How do two quotes compare, line by line?
By the Analistable team · Updated · 3 min read
Match the two quotes on item (use a mapping if the suppliers describe items differently), compare line totals, and list items that appear in only one quote. In the example, Quote B is 500 cheaper overall (7,900 vs 8,400), but it leaves out installation (600) and adds delivery (150) — so like for like, B is dearer.
Part of our guide: How to answer questions across multiple spreadsheets
The data
| Item | Quote A: qty × price | Quote B: qty × price |
|---|---|---|
| Desk | 10 × 420 | 10 × 395 |
| Chair | 20 × 180 | 20 × 190 |
| Install | 1 × 600 | — |
| Delivery | — | 1 × 150 |
In SQL
SELECT COALESCE(a.item, b.item) AS item,
a.qty * a.price AS quote_a,
b.qty * b.price AS quote_b
FROM q1 a
FULL OUTER JOIN q2 b ON b.item = a.item;| item | quote_a | quote_b |
|---|---|---|
| Desk | 4200 | 3950 |
| Chair | 3600 | 3800 |
| Install | 600 | NULL |
| Delivery | NULL | 150 |
Totals: A = 8,400, B = 7,900. Adding A's installation price to B gives 8,500 for a comparable scope — 100 more than A.
In a spreadsheet
Items =UNIQUE(VSTACK(QuoteA[Item], QuoteB[Item]))
Quote A =SUMIFS(QuoteA[Line total], QuoteA[Item], A2)
Quote B =SUMIFS(QuoteB[Line total], QuoteB[Item], A2)
Only in =IF(COUNTIF(QuoteA[Item], A2) = 0, "B only", IF(COUNTIF(QuoteB[Item], A2) = 0, "A only", ""))Compare scope, not just price
- Items only in one quote change the comparison more than small unit-price differences.
- Check quantities as well as prices — a quote for 18 chairs instead of 20 looks cheaper.
- Normalise VAT, currency and payment terms before comparing totals.
Ask it in Analistable: “Compare these two quotes by item and list anything that's only in one of them.” Price lists over time: comparing two price lists.
Unit prices and quantities
| Item | Qty A | Qty B | Price A | Price B | Unit price difference |
|---|---|---|---|---|---|
| Desk | 10 | 10 | 420 | 395 | -25 |
| Chair | 20 | 20 | 180 | 190 | 10 |
B is cheaper on desks and dearer on chairs. If you can split the order, buying desks from B and chairs from A beats both quotes — something a total-only comparison never shows.
Map item names first
| Supplier wording | Quote | Your item |
|---|---|---|
| Office desk 160×80, oak | A | Desk |
| Desk – 1600 oak effect | B | Desk |
| Task chair, mesh | A | Chair |
| Ergo mesh chair | B | Chair |
Suppliers rarely use the same descriptions. A small mapping table, joined to each quote before comparing, turns a messy text match into an exact one. Keep it — next year's quotes from the same suppliers reuse most of the wording. If one supplier quotes 160 cm desks and the other 140 cm, they are different items: map them separately so the difference is visible.
A like-for-like total
| Quote A | Quote B | |
|---|---|---|
| Quoted total | 8400 | 7900 |
| Add missing installation (A's price) | 0 | 600 |
| Comparable total | 8400 | 8500 |
B's delivery charge of 150 is already in its total; A presumably includes delivery in its prices — ask, rather than assume. Once both quotes cover the same scope, A is 100 cheaper.
Checklist before choosing
- Same quantities on every line (catch the 18-vs-20 chairs case with a Qty A − Qty B column).
- Prices on the same VAT basis and currency; quote validity dates still open.
- Payment terms, warranty and lead time — a cheaper quote with a 10-week lead time may cost more in practice.
- Excel 2019: build the item list by pasting both item columns and using Remove Duplicates, then SUMIFS as above.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Item appears as “A only” and “B only” | Descriptions differ | Add both to the mapping table |
| Totals don't match the PDFs | Discount line or rounding missed | Include discounts as their own item |
| Line total wrong | Price per pack vs per unit | Normalise to price per unit |
Frequently asked questions
- How do I compare two quotes in Excel?
- Build one item list from both quotes, sum each quote's line totals per item with SUMIFS, and flag items that appear in only one quote.
- What if suppliers describe items differently?
- Create a mapping table from each supplier's description to your own item name before comparing.
- Why use a full outer join?
- It keeps items that appear in only one quote, which is where most scope differences hide.
- How do I compare quotes with different scope?
- Add the missing items to the narrower quote at the other supplier's price to get a like-for-like total.
- Should delivery be its own line?
- Yes. Separate delivery, installation and discounts so they can be compared directly.
- Can I split an order between suppliers?
- Often, yes: compare unit prices line by line and check that delivery and installation still work when split.