Compare and replace

Hello,

I am new to advanced Excel so I need some help with one of the documents.

I have 2 Excel files that have 2 columns in each.

Product# and Price.

The Product#'s in the 1st sheet are in correct order and have outdated prices.
In the 2nd file the Product#'s are same but they cannot be sorted the same way as the 1st file. So I cannot just copy the column of updated prices from 2st one and paste it into the 1st.

So, my best bet would be to run a script or something that will get a Product# from 2nd file and find it in 1st, if it matches then copy the new price to the 1st file.

I don't even really know where to begin with this...

Thanks

Maybe you can add a temporary helper column using Vlookup to find possible matches...

Assuming your files has data in column A and B

=IF(Isnumber(Match(A2,Sheet2!A:A,0)),Vlookup(A2,Sheet2!A:B,2,0),B2)

copied down

This looks to see if a match for A2 is found in Sheet2, column A, if there is it will take the value in column B and bring it back, if not, it will maintain the value in B2 of your current sheet.

Then you can copy this column and Edit|Paste Special >> Values over the original and then delete the helper column.

You'll probably receive a response on this issue soon. However, if you don't and wish to attach an abbreviated version of your two files, I'll be glad to look at the issue for you. I'm a Kinetic person. If I have a sample of your data, I'll probably be able to figure it out.

J

Well, it seems you copy/pasted my formula without adjusting for the row you are in...

my formula referenced A2 and B2, assuming you'd be starting your formulas in C2...

Instead you are starting in C1, so formula should be adjusted...

=IF(Isnumber(Match(A1,Sheet2!A:A,0)),Vlookup(A1,Sheet2!A:B,2,0),B1)

As NBVC indicated, adjusting the formulas will pull the correct pricing. If you wish to use a VBA approach, the following code will accomplish that:

Please Login or Register  to view this content.

This procedure takes a lot of resources due to the size of your file. It took 4 minutes to complete on my 4G, dual processor, 1.66 Ghz laptop but it did complete. I'm sure there is a better way to write the included code as I'm certain there is a better approach to the problem with different code.

In testing the code, I placed the new prices in column D of worksheet 1. To place the pricing in colume B, change this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Are you familiar with how to install the code? If not, right click on a sheet tab, select "view code" (or click ALT/F11). This will open VBA. Select "Insert Module" (top command bar) then copy, paste the code from this post into the module.

You can run the macro by selecting "run", "run sub/user form" (top command bar). Sit back and relax...it'll take a while. You can also insert a command button on a worksheet and assign the macro to the button. Click and run.

Hope this helps. J

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save