Excel BOM Comparison Analysis

I have several Bills of Materials for electronic assemblies that need to be merged and compared in a single, well-structured Excel file. Each list contains part numbers, manufacturer details, and cost information, and my goal is to understand how the components (and their pricing) differ from one revision or supplier to the next.

What I need from you:
• Build a master workbook that pulls all source BOMs together, normalises the naming conventions, and flags duplicates or mismatches.
• Use VLOOKUP/XLOOKUP, Pivot Tables, and any other native Excel or Power Query techniques you prefer to create a clear side-by-side comparison view.
• Summarise key findings—price deltas, alternate manufacturers, missing parts—in pivot tables or dashboards that I can refresh easily when new data arrives.

Deliverables
1. A clean, documented Excel workbook (latest .xlsx format).
2. At least two comparison views: one focused on cost variance, one on manufacturer or part changes.
3. Short usage notes so I can maintain the file without breaking formulas.

Acceptance criteria
• No hard-coded paths; the file must run on any standard Windows installation of Excel 2016 or newer.
• All lookups return accurate results across every supplied BOM.
• Pivot tables refresh without errors, updating all summary sections automatically.

If you have experience working with electronic component data, that will be a plus, but solid Excel mastery and an eye for clean data structure are the essentials here.