Update your price list from a supplier's new file

Why pasting the new file is the mistake

The supplier's file is sorted their way, contains items you never buy, lacks items you still stock, and may have changed a pack size or a price unit. Pasting it over your list — or sorting one sheet and not the other — puts prices on the wrong rows without a single error message. The safe pattern is a lookup from your list into theirs, so every row of yours either finds its new price or says why it did not.

The two sheets

Master is your list for this supplier: your item code (A), the supplier's code (B), description (C), pack quantity (D) and current cost (E). New is the supplier's file, copied in unchanged — keep the original file too; below, its price is in E and its pack quantity in H. In both sheets, add a helper column Key in F, built from the supplier's code, so both sides are compared in the same shape:

Key   =UPPER(SUBSTITUTE(SUBSTITUTE(TRIM(B2)," ",""),"-",""))

Import code columns as text. A code that Excel already turned into 1.23E+05 or stripped of its leading zeros cannot be repaired by a formula — open the supplier's file again with that column set to Text. If the supplier has renumbered its items, key on the manufacturer part number instead (see which identifier to match on).

Bring the new price in, keep what does not match

On Master, in columns G to J:

New price    =XLOOKUP($F2, New!$F:$F, New!$E:$E, "")
New pack     =XLOOKUP($F2, New!$F:$F, New!$H:$H, "")
Status       =IF(G2="", "Not in new list",
              IF(H2<>$D2, "Pack changed",
              IF(G2>$E2, "Increase", IF(G2<$E2, "Decrease", "Unchanged"))))
Proposed     =IF(I2="Not in new list", E2, IF(I2="Pack changed", "check", G2))

Proposed keeps the current cost for items the supplier did not list, so a missing row never becomes a zero in your ERP. (If the supplier's file has no pack column, leave out the pack test — and check the unit column instead.) "Pack changed" is checked before the price: a box of 10 at 24.00 becoming a box of 12 at 27.60 is a 15 % higher box price but 4.2 % less per piece (2.40 → 2.30). Compare per unit — price per 100 and pack size changes has the formula for every price basis.

On New, mark the supplier's items that are not on your list yet:

On master?   =IF(ISNA(XMATCH($F2, Master!$F:$F)), "New to us", "")

What the statuses look like

"Not in new list" is a question, not a deletion: the item may be discontinued, renumbered, or just missing from this edition. Ask before you stop ordering it. "Pack changed" rows stay out of the update until the unit cost is worked out and the supplier has confirmed the new pack.

Review before you write anything back

• Filter Status: read every "Pack changed" and "Not in new list" row.
• Sort increases by money, not percentage: (New − Current) × your yearly quantity. The line that costs you most is rarely the one with the biggest percentage.
• Treat any change beyond about ±40 % as a unit, pack or price-basis question first (per 100 vs each is a ×100).
• Check the currency and the date the supplier says the prices apply from.

Write back as values, with a date

1. Save a copy of Master before the update, named with the date.
2. Add two columns: Previous cost and Effective from. Copy the current cost into Previous cost, then paste Proposed over the current cost as values (Paste Special → Values), so the lookup formulas do not travel with the prices.
3. Fill Effective from with the supplier's date for every row that changed.
4. For the ERP, export only the rows that changed, with the columns your ERP's import expects — usually your item code, the supplier, the new cost, the currency, the unit and the effective date. Check the import format in your ERP's documentation; it differs between systems.

With tens of thousands of rows: Power Query

Load both sheets as queries, then Merge Queries from Master to New on the Key column with a Left Outer join: every Master row stays, with the new price where one exists — the same "keep what does not match" rule as the formulas. A second merge from New to Master with a Left Anti join lists the supplier's items that are new to you. Refresh both queries next time the supplier sends a file.

Mistakes that put prices on the wrong items