Need to compare 2 very large worksheets

[Kicker]

We use SAP at work. In order to generate some of our reports, we export some very large List Edits to Excel Spreadsheets and then link them into MS Access. Every day we export about 30,000 records.

On a day to day basis, I need to know "what" has changed from one day to the next. In reality, I am now looping through the Work Order column of one and then trying to find it in the Work Order of the other.

Is there a way of "comparing" one worksheet to another and then marking the cells that are different?

I am just getting started on this project and will be adding more ideas and questions to the Access page.

[Paul_Hossler]

Got an example workbook with 100-200 samples?

Paul

[Kicker]

Here is a quick mock up. In reality, there are over 20,000 rows and more than 30 columns for each file. Ignoring all other items, I need to determine.

1. are there any records in Sheet 1 that are not in Sheet 2?
2. are there any records in Sheet 2 that are not in Sheet 1?
3. Are any of the records different. For example if Col C has changes, I need to know about it.

Thanks.

[rbrhodes]

Hi K,

Can you post the code you're using? Maybe we can speed it up!

[Champers]

I am not sure if this helps, however this was passed to me and works quite well.

You may be able to use it.


[vba]
Sub Compare()
Dim rCell As Range
Dim rRng As Range
Dim wksRetain As Worksheet
Dim wksToCheck As Worksheet
Const WKS_ORIG As String = "Sheet1" '<---Change names to suit
Const WKS_COPY As String = "Sheet2"

Set rRng = ThisWorkbook.Worksheets(WKS_ORIG).Range("A1:Z100") 'this can be any range you need

With ThisWorkbook.Worksheets(WKS_COPY)

For Each rCell In rRng

If Not rCell.Value = .Range(rCell.Address).Value _
And Not .Range(rCell.Address).Value = vbNullString Then


rCell.Value = rCell.Value & Chr(10) & "DATACHANGE" & Chr(10) & Format(Date, "MM/DD/YY") _
& Chr(10).Range(rCell.Address).Value & _
Chr(10)
End If
Next
End With
End Sub
[/vba]

Kind Regards

Champers

[Paul_Hossler]

I made a few minor tweaks to consider. I just shaded the cells for testing, but you can replace that code.

But, the big question I have is that there seems to be an assumption in your code that the Order# are 1-1 between the two sheets, e,g, Sheet1 A56 = 800011204, but Sheet2 A56 = 800011205.

To mark the Adds and Deletes will take a little more work.

Let me know if that's what you're looking for. I have code to do that and I can make it compatible with your data if you want. I pulled it out into a seperate WB but you can use it as a starting point if you want. NOTE: I have my share of assumptions and preferences built into this also

There's a macro button on Sheet1 to launch a Form, and it makes a Comparision sheet

[vba]

Sub Compare()
Dim rCell As Range
Dim rRng As Range
Dim wksRetain As Worksheet
Dim wksToCheck As Worksheet
Const WKS_ORIG As String = "Sheet1" '<---Change names to suit
Const WKS_COPY As String = "Sheet2"


Set wksToCheck = Worksheets(WKS_COPY)

Set rRng = ThisWorkbook.Worksheets(WKS_ORIG).Cells(1, 1).SpecialCells(xlCellTypeConstants)
For Each rCell In rRng
With rCell
If .Value <> wksToCheck.Cells(.Row, .Column).Value Then
.Interior.ColorIndex = 3 ' just for testing
End If
End With
Next
End Sub
[/vba]

Of couse, if you find any problems, I'd like to hear so that I can fix mine

Paul

[mdmackillop]

Are order numbers unique on each sheet?