How to Merge Excel Price Lists and Update Prices with a Markup
Question details
The user needs to combine an updated manufacturer price list with an existing pricing sheet and automatically apply varying or uniform profit margins across thousands of parts.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Updating inventory or catalog prices based on a new manufacturer spreadsheet and applying custom markups without manual data entry.
- Observed behavior
- The user is looking for an automated method to match part numbers, retrieve updated costs, and calculate the new selling prices efficiently.
Ensure both your existing price list and the new manufacturer list share a consistent, unique identifier (like a Part Number or SKU) without any hidden spaces or mismatched formatting.
Merge Prices and Apply Margins using VLOOKUP
Best for securely retrieving updated prices based on matching part numbers and calculating new selling prices dynamically.
By utilizing the VLOOKUP or XLOOKUP function, you can match part numbers between your old list and the manufacturer's new list, extracting the updated cost into your current sheet.
Open both the existing pricing sheet and the new 2022 manufacturer spreadsheet. Ensure the Part Number column is to the left of the Price column in the new sheet.
In a new column next to your existing parts list, enter the formula to retrieve the new cost: =IFERROR(VLOOKUP(A2, 'NewPricesSheet'!A:B, 2, FALSE), ""). Replace A2 with your first part number cell, and 'NewPricesSheet'!A:B with the columns in the new list.
To calculate the new selling price with a markup (e.g., 35%), modify the formula to multiply the result by 1.35: =IFERROR(VLOOKUP(A2, 'NewPricesSheet'!A:B, 2, FALSE) * 1.35, "").
Double-click or drag the fill handle at the bottom-right corner of the formula cell to apply this calculation to the thousands of parts in your list.
Apply a Uniform Markup using Paste Special
A rapid method to apply a flat percentage increase across an entire column of updated prices without relying on ongoing formulas.
Merge using the Append and Remove Duplicates Method
Useful if you want to combine the lists physically into one table and keep only the latest pricing records.
Merge and Update Price Lists Seamlessly with WPS Spreadsheet
WPS Office provides powerful data handling tools, including advanced VLOOKUP functions, seamless Paste Special operations, and intuitive Data sorting, allowing you to merge thousands of price list rows effortlessly.
- 1. Open your files: Launch WPS Spreadsheet and open both your legacy price sheet and the new manufacturer Excel file.
- 2. Match part numbers: Use the built-in VLOOKUP or XLOOKUP function to accurately pull new prices into your master list.
- 3. Batch calculate markups: Utilize the Paste Special > Multiply feature to instantly apply your standard profit margin multiplier to the new costs.
- 4. Save and export: Save your updated list in .xlsx format or export it as a CSV for easy upload into your sales or inventory system.
Frequently Asked Questions
Why does my VLOOKUP formula return an #N/A error when matching part numbers?
This usually happens if there are trailing spaces or formatting differences (e.g., one list formats part numbers as text, the other as numbers) between the two sheets. Use the TRIM function on both columns or convert them to the same format to ensure a perfect match.
How do I handle parts that are in the new list but not the old one?
If you are using VLOOKUP on the old list, new parts won't appear. Instead, copy the Part Numbers column from the new list to your master sheet, run 'Remove Duplicates' to get a unique list of all parts, and then VLOOKUP against both the old and new sheets.
Can I apply different markup percentages based on the product category?
Yes. Create a separate reference table that lists each product category alongside its specific margin multiplier. You can then use a nested formula combining VLOOKUP (to find the product category margin) and VLOOKUP (to find the base cost) to calculate the unique final price.
What is Power Query and should I use it for price lists?
Power Query is an advanced data automation tool available in modern spreadsheets. It is highly recommended if you receive standard updated price lists monthly or quarterly, as you can build a repeatable 'Merge Query' workflow based on the part numbers that updates your master file with a single click.
