how to do a reconciliation

[Todd]

Hi. I am trying to reconcile two spreadsheets and can't figure out what to
do. I used the sumproduct formula to match names and bring data from a third
worksheet in and now I want to search worksheet2 for a name and check to see
if the rest of the data across the rows exactly match worksheet1. Can this
be done? The names are not in the same sequence.

Worksheet 1
Name $amount1 $amount 2 $amount 3
Tom 22 25
30
Steve 16 18
20
Tim 10 12
14

Worksheet 2
Name $amount1 $amount 2 $amount 3

Steve 16 18
20
Tim 14 102
104 INCORRECT
Tom 22 25
30



TIA


Todd L.

 

[Frank Kabel]

Hi
in worksheet 2 try the following formula in E1:
=IF(SUMPRODUCT(--('sheet1'!$A$1:$A$100=A1),--('sheet1'!$B$1:$B$100=B1),
--('sheet1'!$C$1:$C$100=C1),--('sheet1'!$D$1:$D$100)),"Correct","Incorr
ect")

 

[Todd]

=SUMPRODUCT(--(('[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$C$1:$C$500)=($B2)),--(('[Phoenix-Tucson -
CO Sept 2004 VoluntaryProducts.tl.xls]Sept2004'!$D$1:$D$500)=($C2)))

Ok, I get this to return ones and zeros. When I try to put in the
"correct","incorrect" I get Value. What am I missing?

Thanks again.

Todd L.


=SUMPRODUCT(--(('[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$C$1:$C$500)=($B2)),--(('[Phoenix-Tucson -
CO Sept 2004 VoluntaryProducts.tl.xls]Sept2004'!$D$1:$D$500)=($C2)))

 

[Todd]

Ha! I missed the "if"! I am not bright today. What are the limits to this?
I want as many as 18. I get to 12 and I am getting messages like "formula
to long"


Todd.

=SUMPRODUCT(--(('[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$C$1:$C$500)=($B2)),--(('[Phoenix-Tucson -
CO Sept 2004 VoluntaryProducts.tl.xls]Sept2004'!$D$1:$D$500)=($C2)))

Ok, I get this to return ones and zeros. When I try to put in the
"correct","incorrect" I get Value. What am I missing?

Thanks again.

Todd L.


=SUMPRODUCT(--(('[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$C$1:$C$500)=($B2)),--(('[Phoenix-Tucson -
CO Sept 2004 VoluntaryProducts.tl.xls]Sept2004'!$D$1:$D$500)=($C2)))

Hi
in worksheet 2 try the following formula in E1:
=IF(SUMPRODUCT(--('sheet1'!$A$1:$A$100=A1),--('sheet1'!$B$1:$B$100=B1),
--('sheet1'!$C$1:$C$100=C1),--('sheet1'!$D$1:$D$100)),"Correct","Incorr
ect")

 

[Frank Kabel]

Hi
the limit is 1,024 characters for a formula. No way around this. You
may try using defined names though this probably won't work as your
other file is closed.

another idea would be to create a new column (column 19) with a formula
such as
=A1 & "^" & B1 & "^" & C1.....
in both files and compare only this column with each other

--
Regards
Frank Kabel
Frankfurt, Germany

Ha! I missed the "if"! I am not bright today. What are the limits to this?
I want as many as 18. I get to 12 and I am getting messages like "formula
to long"


Todd.

=SUMPRODUCT(--(('[Phoenix-Tucson - CO Sept 2004

VoluntaryProducts.tl.xls]Sept2004'!$C$1:$C$500)=($B2)),--(('[Phoenix-Tu
cson -

CO Sept 2004 VoluntaryProducts.tl.xls]Sept2004'!$D$1:$D$500)=($C2)))

Ok, I get this to return ones and zeros. When I try to put in the
"correct","incorrect" I get Value. What am I missing?

Thanks again.

Todd L.


=SUMPRODUCT(--(('[Phoenix-Tucson - CO Sept 2004

VoluntaryProducts.tl.xls]Sept2004'!$C$1:$C$500)=($B2)),--(('[Phoenix-Tu
cson -

CO Sept 2004 VoluntaryProducts.tl.xls]Sept2004'!$D$1:$D$500)=($C2)))

Hi
in worksheet 2 try the following formula in E1:

=IF(SUMPRODUCT(--('sheet1'!$A$1:$A$100=A1),--('sheet1'!$B$1:$B$100=B1),
--('sheet1'!$C$1:$C$100=C1),--('sheet1'!$D$1:$D$100)),"Correct","Inco rr

ect")

--
Regards
Frank Kabel
Frankfurt, Germany

Hi. I am trying to reconcile two spreadsheets and can't figure out
what to
do. I used the sumproduct formula to match names and bring data from
a third
worksheet in and now I want to search worksheet2 for a name and check
to see
if the rest of the data across the rows exactly match worksheet1.
Can this
be done? The names are not in the same sequence.

Worksheet 1
Name $amount1 $amount 2
$amount 3
Tom 22 25
30
Steve 16 18
20
Tim 10 12
14

Worksheet 2
Name $amount1 $amount 2
$amount 3

Steve 16 18
20
Tim 14 102
104 INCORRECT
Tom 22 25
30



TIA


Todd L.