Replace value in one cell with value of other cell

[Bax]

Hi all,

I'm running into a small problem and I know that the answer is most likely to be found here

I need to compile a master file which basically has 2 columns. Column A is a part number and column B is the price of that part number.

I have a 2nd file with also a column with part number and a column with prices. These prices are updated prices, so I need to substitute the prices in my master file with the prices of this 2nd file.
A simple VLOOKUP would do the trick you think... but the issue is that my master file contains part numbers which are not appearing in the 2nd file. If that is the case, the price for that part number needs to remain the same as it is in the master file.
With the VLOOKUP (and copying this down in my master file), I will see #N/A for the parts which are in my master file but not in my 2nd file. I need these to remain the original prices which were in that cell.

So in short, how can I have the part numbers in my master file look into my 2nd file and only update the prices for the part numbers which it finds in the 2nd file and leave all other prices in the master file as is.

Not sure if the above is clearly explained, if not, I'll be more than happy to explain further.

Any help is highly appreciated!!

Many thanks in advance for your time and effort in this.
Peter

[StephenR]

Without the specifics of your situation, to put into plain English, perhaps a formula along the lines of

IF(ISNA(VLOOKUP(...)),A1,VLOOKUP(...))

?

[Bax]

Hi Stephen,

Thanks for the fast reply.
However, the above formula won't do what I want...
I've attached a file with 2 sheets. The first sheet called "MASTER FILE" has parts and prices. The 2nd sheet called "2ND FILE" has updated prices for some of the parts in the first sheet.

So now I need the prices in the first sheet to be replaced with the prices of the same parts in the 2nd sheet, but the prices of the parts in the first sheet which doesn't appear in the 2nd sheet must remain as is.
In the example file, on the first sheet, parts A through E would get the new price, but the prices for parts F through L would need to remain the same. How can I have the prices in column B be replaced with the new values from the 2nd sheet without altering any of the prices of the parts which doesn't appear in the 2nd sheet?

This example has only a few lines, but in reality I have > 7000 lines in my master file. So manually filtering through the lines isn't an option..

I hope you can shine some light....

[StephenR]

You could put this formula in C and then copy and paste values over column B

=IF(ISERROR(VLOOKUP(A2,'2ND FILE'!$A$2:$B$6,2,FALSE)),B2,VLOOKUP(A2,'2ND FILE'!$A$2:$B$6,2,FALSE))

Or alternative approach using VBA:

Please Login or Register  to view this content.

[Bax]

Great, thanks a mill Stephen!

This will do.
Case closed...