VLOOKUP and IF, THEN?

I have a simple pricelist in 2 columns showing part number and price. I receive a new sheet every week with updated prices, but the new list may be longer or shorter than the previous one, or include new items. And some prices change and some don't. Is there any way to run a formula so that:

If the new list includes part numbers from the previous week, then the price will update?

I can identify new part numbers easily

The updated information would be most useful in a new column, so I have an historical record of price changes over time.

I am using Excel 2007

Thank you

Several ways of doing this. Could do it with some VBA to automate the process, or manually.

On the proviso your part numbers are a unique identifier then you can use VLOOKUP or INDEX/MATCH to feed the new values in to the old list. This can be in a new column or an existing one.

Post some sample data and we can provide tailor made formulas for you.

BSB

Ok, great.

Here's a screen shot. Black data is old 'master' list and the red is a sample of the new

Screen Shot 2016-03-14 at 12.28.00.png

Thanks!

In B2 and copy down:

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

BSB

It works perfectly, thank you!!

Happy to help.

If it's a regular action you need to perform then a little macro to take the effort out might be a good idea.
You could automate the whole thing into a button click and a second or so of processing time.

BSB

That also sounds good, as yes it is a regular thing that needs doing. How would I go about that?

Would I be correct in assuming that the weekly price list update is actually a separate file?
Is there a regular name for that file or could/would it vary week to week?
Is this separate file stored in a set folder?

BSB

Yes, it's sent through as a separate excel sheet by email
The name of the file could vary but it would be no big deal to save it and change the name in that case.
It can also be saved in set folder, but then would the name have to be changed every week? Or just overwrite the previous one I suppose, if the data is being extracted to the new master list

Save the two attached files into the same folder.
Open the "Master" one and click the button.

The code is very basic so should be easy enough for you to see which bits would need amending to adapt this to your real workbooks.

The idea is the code will open the new price list (this file can be overwritten as new ones come in), check for new items and add these to the bottom of the list in the master file, then look up the relevant values and close the new list.

It's not perfect, i.e. it doesn't check if the file new list has already been imported, or indeed if the file is there at all, but it should be a good starting point for you.

You can view the code by pressing Alt + F11 to bring up the VB Editor.

Shout for help if any of it needs explaining.

BSB