Comparing two spreadsheets

[barb]

I have extracted data from two systems into two seperate excel spreadsheets.
they should be identical but are not. I would like to compare them to see
which spreadsheet is missing information.
Sheet 1 has - (a1) emp no (b1) hours (c1) date (d1) project id
Sheet 2 has the same columns but has 60 extra rows
I have sorted both sheets by emp no, date and project id
I used a pivot table to show that multiple emp nos do not have the same
hours over the two spreadsheets. I need to compare the information to ensure
(a1) emp no, (c1) date and (d1) project id are all equal and then determine
where I am missing rows of information or if the information there is
incorrect. Can anyone help me?

 

[Bernie Deitrick]

Barb,

In the sheet with the extra rows, try a formula like this in E2:

=SUMPRODUCT(($A$2:$A$2000=A2)*($B$2:$B$2000=B2)*($C$2:$C$2000=C2)*($D$2:$D$2000=D2))

and copy down (I have assumed you have fewer than 2000 rows of data - that can be changed, of
course). If you get anything other than 1, there are duplicated values in your data table.

Then use a similar formula in each of your workbooks, referencing the other workbook: In Book2, use

=SUMPRODUCT(([Book1.xls]Sheet1!$A$2:$A$2000=A2)*([Book1.xls]Sheet1!$B$2:$B$2000=B2)*([Book1.xls]Sheet1!$C$2:$C$2000=C2)*([Book1.xls]Sheet1!$D$2:$D$2000=D2))

And in Book1, use
=SUMPRODUCT(([Book2.xls]Sheet1!$A$2:$A$2000=A2)*([Book2.xls]Sheet1!$B$2:$B$2000=B2)*([Book2.xls]Sheet1!$C$2:$C$2000=C2)*([Book2.xls]Sheet1!$D$2:$D$2000=D2))

If any of those formulas return 0, then that row's data doesn't appear in the other workbook

HTH,
Bernie
MS Excel MVP

 

[barb]

Thank you - it worked perfectly.
barb

Barb,

In the sheet with the extra rows, try a formula like this in E2:

=SUMPRODUCT(($A$2:$A$2000=A2)*($B$2:$B$2000=B2)*($C$2:$C$2000=C2)*($D$2:$D$2000=D2))

and copy down (I have assumed you have fewer than 2000 rows of data - that can be changed, of
course). If you get anything other than 1, there are duplicated values in your data table.

Then use a similar formula in each of your workbooks, referencing the other workbook: In Book2, use

=SUMPRODUCT(([Book1.xls]Sheet1!$A$2:$A$2000=A2)*([Book1.xls]Sheet1!$B$2:$B$2000=B2)*([Book1.xls]Sheet1!$C$2:$C$2000=C2)*([Book1.xls]Sheet1!$D$2:$D$2000=D2))

And in Book1, use
=SUMPRODUCT(([Book2.xls]Sheet1!$A$2:$A$2000=A2)*([Book2.xls]Sheet1!$B$2:$B$2000=B2)*([Book2.xls]Sheet1!$C$2:$C$2000=C2)*([Book2.xls]Sheet1!$D$2:$D$2000=D2))

If any of those formulas return 0, then that row's data doesn't appear in the other workbook

HTH,
Bernie
MS Excel MVP

I have extracted data from two systems into two seperate excel spreadsheets.
they should be identical but are not. I would like to compare them to see
which spreadsheet is missing information.
Sheet 1 has - (a1) emp no (b1) hours (c1) date (d1) project id
Sheet 2 has the same columns but has 60 extra rows
I have sorted both sheets by emp no, date and project id
I used a pivot table to show that multiple emp nos do not have the same
hours over the two spreadsheets. I need to compare the information to ensure
(a1) emp no, (c1) date and (d1) project id are all equal and then determine
where I am missing rows of information or if the information there is
incorrect. Can anyone help me?

.