Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing information from one doc to another if conditions apply
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing information from one doc to another if conditions apply
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing information from one doc to another if conditions ap
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? "AndyM" wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing information from one doc to another if conditions ap
I think it would be something like:
=IF(ISERROR(VLOOKUP($A5,Reroute!$A$2:$V$362,7,FALS E)), $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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing information from one doc to another if conditions ap
Yes, that is right.. you have to include the ISERROR function within the
formula as well. Sorry, should have included that. Andy "dan dungan" wrote: I think it would be something like: =IF(ISERROR(VLOOKUP($A5,Reroute!$A$2:$V$362,7,FALS E)), $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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing information from one doc to another if conditions ap
Awesome -- I'll try it today. Thanks for the help!
"AndyM" wrote: Yes, that is right.. you have to include the ISERROR function within the formula as well. Sorry, should have included that. Andy "dan dungan" wrote: I think it would be something like: =IF(ISERROR(VLOOKUP($A5,Reroute!$A$2:$V$362,7,FALS E)), $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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing information from one worksheet to another if conditions | Excel Worksheet Functions | |||
How many conditions can I apply to a cell? I need to apply 8. | Excel Discussion (Misc queries) | |||
Replacing information | Excel Programming | |||
Summing if several conditions apply | Excel Worksheet Functions | |||
How to apply conditions on Input box? | Excel Programming |