Updating Current Master Price File

Originally Posted by opsolutions

1.Every quarter, we need to update the pricing cost on about 25,000 to 40,000 items in our master price file.
2.We have about 12 different vendors that provide Excel-formatted pricesheets quarterly for some or all of the items in our master price file.
3.The problem becomes how to update my master pricing database with the prices from each vendor's pricing spreadsheet for the new quarter. For example, currently, we are nearing the end of the 4th quarter, 2006. Once I have downloaded the price files for each vendor for the 1st quarter, 2007, the dilemma becomes how to import the prices from each vendor's spreadsheet into the master database based upon matching item#. Allow me to illustrate this as follows:
Let's say we have the following 5 item numbers in our master pricing spreadsheet for the 4th quarter, 2006:
AVE 5160
AVE 5161
AVE 5162
AVE 5164
AVE 5165
In Vendor A's pricing spreadsheet for the 1st quarter 2007, they only have pricing for the following item#'s:
AVE 5160
AVE 5162
AVE 5165
In Vendor B's pricing spreadsheet for the 1st quarter 2007, they only have pricing for the following item#'s:
AVE 5161
In Vendor C's pricing spreadsheet for the 1st quarter 2007, they only have pricing for the following item#'s:
AVE 5160
AVE 5161
AVE 5162
AVE 5164
Is there a way in Excel 2000 that formulas could be devised that would bring each vendor's pricing into the master pricing spreadsheet so that we can then run the necessary commands to determine which items have price changes and ultimately update the pricing for each item to the vendor offering the most favorable pricing for each item?

Hi,

with a column for each Vendor file, use a VLookup from the master back to the incoming price files.

hth
---

Hi,

This link and example may help

http://www.contextures.com/xlPivot08.html

http://www.contextures.com/PivotMultConsolPivot.zip

http://www.contextures.com/PivotMultConsolProv.zip

Think you might be over excel row limit unless your using the new version. Not sure if this will be a issue for a MS query


VBA Noob