Match Text In Column Of Another Workbook & Return Nth Column Same Row

[johnbesard]

Hello OZgrid,
Awsome that I found this place through Google search. Thanks Dave.

I have two excel files.
File #1 is a varying length and has 4 columns with ID #s in Column A.
File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B.

I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2.

I've tried using the substitute function but I could only replace one found ID at a time.

I hope someone will be able to help me out.

Thanks
JB

[Bill Rockenbach]

John, Welcome to ozGrid Tell your friends about us.

In file1 insert a blank column B
In cell B1 enter this formula and copy down

=VLOOKUP(A1,[file2.xls]Sheet1!$A:$B,2,FALSE)

Hope this helps y'all

[johnbesard]

WOW! You guys really are SUPER FAST!

Bill.
Thanks for your super fast reply!
Your formula works, but maybe I wasn't specific enough. I need to replace the values in column A on file#1 with what is returned by the formula. Is that possible?

I'll be using this file to import into a different software which I have no control over. So I'm not able to add another column to it. (I wasn't specific file parameters not being modifiable, sorry.)



Thanks
JB

[Bill Rockenbach]

After inserting column B and the formula. Then select column B, Copy and paste special values. This will convert the formulas in column B to the values the formula produced. Then Delete column A.

[johnbesard]

Bill,
Thanks a mint! It works. And will help me move my data around tomorrow.

I'm glad I signed up on this forum! Accurate answers and friendly folks offering the advice.

Thanks again!
JB

[Bill Rockenbach]

Your welcome, Glad to help Tell a friend about ozGrid

[johnbesard]

Can this process be automated like a Macro? Since the columns will have varying lengths, I'm afraid a recording a Macro won't work.
I'd really like it to be run from a button or better yet run when file#1 is opened. Now THAT would be convenient.

Should I post this as a seperate thread? I don't want to break any rules or annoy anybody here. There's too much info here to get banned from it.

Thanks again.

[Bill Rockenbach]

Sorry for the delay in getting back to you.

Attached are your two sample files. There is a button on fille2 to map file1.

Hope this works for you.

[johnbesard]

Bill.
THANKS!
I've been real busy with a lot of other things, but this looks awsome! All I have to do is put in the real info!

I hope someone (like me) who searches is able to find this thread! A great solution to this problem...

Thanks
JB

[Bill Rockenbach]

Glas I could help, John