Replacing information from one worksheet to another if conditions

  • Thread starter Thread starter scr
  • Start date Start date
S

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?
 
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 changedto
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?
Click to expand...

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 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.
 
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.
Click to expand...

Hi scr,

I must retract my suggestion!
It only works with numbers.

Ken Johnson
 
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,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))-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
 
Back
Top