Update values in spreadsheet based on new values in second workbook

[JoeSchuch]

We have a master spreadsheet that contains all the common parts and prices we use on a daily basis. Occasionally we received updated pricing from manufactures and our master sheet needs to be appended with the new prices. Our master sheet may contain only a subset of all the parts available in the manufacturer's spreadsheet. I'm trying to create a way to compare the parts in our master list with the parts in the new pricing sheet from the vendor and have it update the pricing column with the new price.

Master.xlsx is our master spreadsheet with COL A being Part # and COL B being price. There are roughly 5000 rows of data. Vendor.xlsx is the new pricing sheet from our manufacturer. The routine should take the value of Row 1, Col A in Master and see if it exists in Vendor. If so, Row 1, Col B should be updated in Master to reflect the value in Col B of the row the value was found in the Vendor file. Once the value is updated, the routine moves to the next row in the Master file. If the value was not found in Vendor, the routine moves to the next row in Master.

Looking for some help in automating this process with scripting or VB.

Thanks,

 

[elmer007]

If I understand this correctly, it seems that a vlookup could solve this. In column C of the Master.xlsx, it would be something like: =IFERROR(VLOOKUP(A2,[Vendor.xlsx]Sheet1!$A:$B,2,FALSE),B2)
This would return the price from the vendor spreadsheet; however, if no part # were found, it would return the price already in column B of the master sheet.

Once this was used, you could simply paste the values from the formula in column C into column B.