How to Create a Master Product Catalog from Multiple Supplier Sheets
Struggling with scattered supplier spreadsheets? Learn how to merge them into one clean, consistent master product catalog in Excel.
How to Create a Master Product Catalog from Multiple Supplier Sheets
If you work with multiple suppliers, chances are your inbox is full of Excel or CSV price lists — each with different formats, headers, and product naming conventions.
Manually merging these files into a single master product catalog is frustrating, time-consuming, and prone to errors.
In this guide, we’ll show you how to merge multiple supplier sheets into one clean, standardized product catalog — ready for analysis, uploads, or ERP imports.
🛑 Why Supplier Data is Such a Mess
- Different column names (
Product Code,SKU,Item ID). - Inconsistent categories (
Shoes,SHOE,Footwear). - Price formats vary (
$10.00,10,10 USD). - Stock units differ (
10 pcs,10 units,10). - Duplicate or missing products across sheets.
If left unchecked, this chaos leads to errors, duplicate products, and wrong pricing in your system.
✅ Step-by-Step Process to Build a Master Catalog
1. Collect All Supplier Sheets
- Put all vendor Excel/CSV files into one folder.
- Ensure filenames are clear (
Supplier_A.xlsx,Supplier_B.csv, etc.).
2. Standardize Column Headers
- Use consistent names:
SKU,Product Name,Category,Price,Stock. - Apply Excel’s
Find & Replaceto unify headers. - Or create a mapping table (e.g.,
Item ID → SKU).
3. Normalize Data Formats
- Convert all currencies into a single format (e.g., USD with 2 decimals).
- Standardize date formats (YYYY-MM-DD).
- Strip units from stock counts (
10 pcs → 10).
4. Merge Sheets
- In Excel: use Power Query → Append Queries to combine multiple files.
- Or manually copy/paste, then use
Remove Duplicates. - Ensure SKUs are unique — if duplicates exist, consolidate them.
5. Create a Golden Schema
Define the final structure of your master catalog.
Example:
| SKU | Product Name | Category | Supplier | Price (USD) | Stock |Map all supplier data into this schema.
6. Validate & Clean
- Run duplicate checks (based on SKU or Product Name).
- Highlight missing fields with conditional formatting.
- Spot-check a few items against original supplier sheets.
📊 Example: Before & After
Before (Supplier Sheets)
| Item ID | Desc | Price | Stock |
|---|---|---|---|
| 123 | Red Shoes | $10.0 | 10 pcs |
| AB-45 | Blue Shoe | 12 USD | 20 |
| Code | Product Name | Cost | Quantity |
|---|---|---|---|
| 123 | Shoes - Red | 10 | 10 |
| 789 | Jacket Blue | 25 | 15 |
After (Master Catalog)
| SKU | Product Name | Category | Supplier | Price (USD) | Stock |
|---|---|---|---|---|---|
| 123 | Red Shoes | Footwear | Supplier A | 10.00 | 10 |
| AB45 | Blue Shoes | Footwear | Supplier A | 12.00 | 20 |
| 789 | Jacket Blue | Apparel | Supplier B | 25.00 | 15 |
🤖 How RowTidy Makes This Effortless
Instead of spending days manually cleaning files, RowTidy does this for you:
- Upload multiple supplier sheets.
- Automatically maps to a Golden Schema.
- Standardizes prices, dates, and stock.
- Flags duplicates and errors.
- Exports your ready-to-use master catalog in Excel/CSV/Google Sheets.
No manual cleanup. No broken formulas. Just clean, consistent product data.
📌 Conclusion
Creating a master product catalog doesn’t have to take hours of manual work.
By standardizing headers, normalizing formats, and merging into a Golden Schema, you get:
- A single source of truth for all supplier data.
- Faster onboarding of new suppliers.
- Fewer errors in pricing and stock.
✍️ Want to build your own master product catalog in minutes?
👉 Try RowTidy today — and let AI do the heavy lifting.