Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replacing information from one worksheet to another if conditions
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.worksheet.functions
|
|||
|
|||
Replacing information from one worksheet to another if conditions
On Sep 29, 8:16*am, 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? One way... In I2 on the sheet with the original list of addresses... =IF(SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),-- (Sheet2!$C$2:$C$11=$C2))=0,D2,SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),-- (Sheet2!$B$2:$B$11=$B2),--(Sheet2!$C$2:$C$11=$C2),Sheet2!D$2:D$11)) Fill across to M2 then down to the bottom of list of original addresses. Then hide the columns D to H of original addresses. As is formula works for maximum of 10 reroutes on a sheet named Sheet2. Edit the formula to suit the number of reroutes and the reroute sheet name. Ken Johnson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replacing information from one worksheet to another if conditions
Any suggestions?
Check your other posts for answers. Regards, Fre. "scr" wrote in message ... 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.worksheet.functions
|
|||
|
|||
Replacing information from one worksheet to another if conditi
Hi Ken,
I tried you formula, it worked for some but not for all -- I got some #Value errors. Cany you explain what your formula actually does in lay mans terms? Maybe it will help me understand it better. "Ken Johnson" wrote: On Sep 29, 8:16 am, 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? One way... In I2 on the sheet with the original list of addresses... =IF(SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),-- (Sheet2!$C$2:$C$11=$C2))=0,D2,SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),-- (Sheet2!$B$2:$B$11=$B2),--(Sheet2!$C$2:$C$11=$C2),Sheet2!D$2:D$11)) Fill across to M2 then down to the bottom of list of original addresses. Then hide the columns D to H of original addresses. As is formula works for maximum of 10 reroutes on a sheet named Sheet2. Edit the formula to suit the number of reroutes and the reroute sheet name. Ken Johnson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replacing information from one worksheet to another if conditi
On Sep 30, 9:13*am, scr wrote:
Hi Ken, I tried you formula, it worked for some but not for all -- I got some #Value errors. *Cany you explain what your formula actually does in lay mans terms? * Maybe it will help me understand it better. "Ken Johnson" wrote: On Sep 29, 8:16 am, 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? One way... In I2 on the sheet with the original list of addresses... =IF(SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),-- (Sheet2!$C$2:$C$11=$C2))=0,D2,SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),-- (Sheet2!$B$2:$B$11=$B2),--(Sheet2!$C$2:$C$11=$C2),Sheet2!D$2:D$11)) Fill across to M2 then down to the bottom of list of original addresses. Then hide the columns D to H of original addresses. As is formula works for maximum of 10 reroutes on a sheet named Sheet2. Edit the formula to suit the number of reroutes and the reroute sheet name. Ken Johnson Hi scr, I must retract my suggestion! It only works with numbers. Ken Johnson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replacing information from one worksheet to another if conditi
Hi scr,
For Text and Numbers use... =IF(SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),-- (Sheet2!$C$2:$C$11=$C2))<0,OFFSET(Sheet2!$A$1,SUM PRODUCT(--(Sheet2!$A $2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),--(Sheet2!$C$2:$C$11= $C2),ROW(Sheet2!D$2:D$11))-1,COLUMNS($A:C)),D2) It's an IF function. The condition being tested can be expressed as... "Are the values on this row in columns A, B and C the same as the corresponding values in any of the rows of the sheet with the reroute data? eg On this row A="John",B="Smith" and C="Teacher". Is there a row on the reroute sheet where A="John",B="Smith" and C="Teacher"? This test is carried out by... SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),-- (Sheet2!$C$2:$C$11=$C2)<0 When the result of the test is TRUE the SUMPRODUCT function... SUMPRODUCT(--(Sheet2!$A$2:$A$11=$A2),--(Sheet2!$B$2:$B$11=$B2),-- (Sheet2!$C$2:$C$11=$C2),ROW(Sheet2!D$2:D$11)) tells the OFFSET function which row on the reroute sheet has columns A, B and C the same as those on the row with the formula. The OFFSET function is anchored at Sheet2!$A$1 and it returns the value on the reroute sheet on the row with the same A,B and C values (the row offset argument is 1 less than the actual row where the correct values were located by the SUMPRODUCT function). The column offset argument is given by... COLUMNS($A:C) increments to COLUMNS($A:D) then COLUMNS($A:E) etc as the formula is filled across the five columns so that the different reroute columns are referred to. COLUMNS($A:C)=3 COLUMNS($A:D)=4 COLUMNS($A:E)=5 etc When the result of the test is FALSE the original data in column D is returned. As the formula is filled across to the other 4 columns this changes to E, F, G then H. See... http://www.xldynamic.com/source/xld.SUMPRODUCT.html for excellent explanation of the SUMPRODUCT function Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replacing value in formula from outside worksheet? | Excel Discussion (Misc queries) | |||
Replacing a corrupt worksheet | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Using information from one worksheet, to rename inserted worksheet | Excel Worksheet Functions | |||
Can I sync information on one worksheet with another worksheet | Excel Worksheet Functions |