matching string from a list and copying adjacent cells

Hi!

I've been given an assignment at work that requires tedious manual address list updating, I have a list of new Contacts and old contacts in two excel sheets. I need to update the new contact list.

I started reading VBA for Dummies and will be updating this thread as I figure something up but I would appreciate any help.

The Problem:

I have two address lists, i.e. company names in range(A1:A2990), and sales managers and their info in adjacent cells i.e. range(B1:D2990). The thing is that if I put the two lists side to side, the same company names of old and new lists are not on the same rows, i.e. new list has more companies and some old companies are not in the new list at all. Also, I have some of the sales managers info already listed in the new list and I need to retain that info.

I think I would need the following kind of program to include all of the old contacts in the new list:

Pull string out of selected cell (sheet1.A1) of the new list and match it with old list (sheet2.(A1:A2990))
IF there is a match,
THEN, IF adjacent cells(sheet1.B1:D1) of the new list are empty, copy cells(sheet2.B1:D1) to (sheet1.B1:D1)
ELSE, Stop
ELSE, Stop

Then move on to cell sheet1.B1 and repeat the procedure.
Stop the whole loop after cell A2990.

At this time I know how to build nested IF statements.
I need to figure out how to pull strings from cells and match them with strings in an array.
Also I need to figure out how to make a loop that will repeat the whole procedure and then move on to the next cell until A2990.

Hope someone can give some pointers.

Derivatives,

Give the following a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Derivatives,

Alternatively, you could use the VLOOKUP formula as a non-macro solution. Assuming your data has headers, you would put the following in cell B2 of the 'New Contacts' worksheet:
=IF(ISERROR(VLOOKUP($A2,'Old Contacts'!$A:$A,1,FALSE)),"",VLOOKUP($A2,'Old Contacts'!$A:$D,COLUMN(),FALSE))

Then you would just copy over and down. See attached for an example of how it works.

Hope that helps,
~tigeravatar

Wow! Thanks a lot, that VBA code worked like a charm! That saved me a lot of time and now I can pick the code apart with VBA help and figure out how it works. Thanks again Tigeravatar, you're amazing!