Replacing information from one doc to another if conditions apply

[scr]

I have an original list of addresses. A "reroute" list is provided. If
columns A, B, & C on the original list match columns A, B, & C on the
"reroute" list the addresses in columns D, E, F, G & H must be changed to
the new addresses listed on the "reroute" list columns D, E, F, G & H.

If, however, there is not match then the address in the original list needs
to stay the same.

Any suggestions?

 

[AndyM]

This can just be done with Excel formulas if you would like, rather than
using VBA.

Create a column in each list that concatenates columns A, B, and C. This
will be used as your key to match up the two lists.

Then use formulas at the end of the reroute list to get the correct address.
The formulas could use VLOOKUP to attempt to find a match. If it finds a
match, the formula can be set to the value from the original list. If it
does not find a match, it can be set to the value from the reroute list.
This will require an IF function within the formula.

This can also be done in VBA without too much trouble. If you want to go
that route, I can get you started with some code.

Hope this helps!

 

[scr]

Hi Andy,

This is the route I ended up going. I did exactly what you state below
combining A, B & C, then used a Vlook-up to find the match between the
original list and the "reroute" list. It works ok but you mention something
I don't know how to get to work -- using the IF statement in conjuction with
the Vlook-up. I ended up finding the ones that needed to be rerouted and had
the address columns entered, but then the ones that didn't have a match came
up with n/a. Then I ended up sorting by n/a -- deleting those cells for
those addresses and then deleting the original address cells for those that a
match was found. Leaves room for error.

The is what my formula looks like:
=VLOOKUP($A5,Reroute!$A$2:$V$362,7,FALSE). So if I wanted it to put the
original address in if the answer was false how would I do that?

 

[dan dungan]

I think it would be something like:

=IF(ISERROR(VLOOKUP($A5,Reroute!$A$2:$V$362,7,FALSE)),
$A5,VLOOKUP(Reroute!$A$2:$V$362,7,FALSE))

That depends on if the original address is in A5. Also, I don't know
the purpose of
having the absolute column reference in $A5.

Dan

 

[AndyM]

Yes, that is right.. you have to include the ISERROR function within the
formula as well. Sorry, should have included that.

Andy

 

[scr]

Awesome -- I'll try it today. Thanks for the help!