When an electrical or mechanical engineer hands over a Rev B Bill of Materials (BOM) spreadsheet, procurement usually asks one question: What actually changed in what I need to buy?

Most small hardware teams and contract manufacturers run a quick VLOOKUP or XLOOKUP between Rev A and Rev B, or paste both sheets into a generic flat CSV diff tool. On real-world production spreadsheets, that approach silently breaks in four ways.

1. Leading Zeros Vanish (00047 vs 47)

Supplier and internal part numbers frequently start with zeros (00047 or 00123). If one revision exports from CAD as text and the other is saved in Excel with General formatting, numeric coercion strips the leading zero. Flat lookups either flag false removals or merge distinct parts by mistake.

2. Unchanged Child Rows Still Change Net Purchase Quantities

Flat diff tools compare rows line by line without parent-child multipliers. Suppose a sub-assembly PCB increases from 2 per unit to 3 per unit in Rev B, while a child resistor row underneath it stays untouched at 4 per board. A row diff reports "Unchanged," yet your actual purchasing requirement per finished unit jumped from 8 to 12.

3. Reference Designator (RefDes) Swaps and Range Mismatches

In PCBA BOMs, engineers pack designators into ranges like R1-R4, R7. Two common errors slip past manual spreadsheet checks:

  • Quantity vs. RefDes mismatch: Qty per Parent says 3, while the RefDes column only lists 2 unique designators.
  • Component swaps at the same anchor: U3 changes from MPN-OLD to MPN-NEW. Without parsing and diffing expanded designator sets per parent instance, buyers miss direct part replacements.

4. Missing Formula Caches in Uploaded .xlsx Files

Spreadsheets exported by scripts or third-party tools sometimes contain formula cells without cached values. Naive parsers read those cells as 0 or blank, corrupting extended batch quantities instead of flagging the line for human review.

How I Structured a Deterministic Fix

To stop fighting broken lookup formulas, I put together two resources for small hardware and manufacturing teams:

  • If you just need a clean starting point for indented assemblies (1, 1.1, 1.1.1), grab this open-source Multi-Level BOM Excel Template on GitHub.
  • If you need to compare two full BOM snapshots (Rev A vs. Rev B) automatically, I built an in-memory Multi-Level BOM Diff Checker using deterministic TypeScript and Decimal math—it builds separate assembly trees, expands RefDes ranges, flags zero-pad or unit conflicts for review, and deletes files from memory immediately with zero data retention.

How is your team currently handling ECO / BOM revision handoffs between engineering and purchasing before adopting a full PLM?