Comparing sheets of data Excel or Access?

[SellOut]

Hello,

I have three versions of what should be similar data and I want to compare them to find where there are differences. Two sheets are in excel and one is currently in access. I was intending to import the three sheets into an excel file on seperate sheets and, as each relative cell should be comparable, make three calculation sheets using the AND [=AND(Sheet1!A1=Sheet2!A1)] function to compare each cell between the three sheets (ie between sheets 1 and 2, 2 and 3, 1 and 3) and then use nested IF statements to classify any variations into another summary sheet.

I have found that as my sheets are quite big (AQ 1500 being the furthest cell) and my computer a bit slow, it is taking a very long time. Does anyone have a better method in either Access or Excel to chart differences between large sheets?

Thanks

SO

 

[Zathras]

What I usually do is open a new (empty) sheet and put this formula in A1:

[tt] =IF(Sheet1!A1=Sheet2!A1,0,1)[/tt]

Then I copy that to all of the necessary cells. Select all and look at the bottom of the screen to see the sum. It should be zero. If not, you can then use the Find feature to look where the cell contents = 1.

Just do this 2 sheets at a time rather than trying to do all 3 at once. (Two things equal to the same thing are equal to each other.)

 

[Zathras]

What I usually do is open a new (empty) sheet and put this formula in A1:

[tt] =IF(Sheet1!A1=Sheet2!A1,0,1)[/tt]

Then I copy that to all of the necessary cells. Select all and look at the bottom of the screen to see the sum. It should be zero. If not, you can then use the Find feature to look where the cell value = 1.

Just do this 2 sheets at a time rather than trying to do all 3 at once. (Two things equal to the same thing are equal to each other.)

 

[xlbo]

I believe there is actually a wizard for creating a query to find differences between 2 tables so if you can import the tables to access, it should be pretty darn simple there.

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question