Compare 2 spreadsheets in XL2K3

[MeGustaXL]

Hiya,
My 2 spreadsheets are 729 rows by 98 columns, and there appear to be some subtle differences between them.

How can I kind of lay one on top of the other and highlight where they differ?

Chris

Someday I'll know what I'm donig...damn!

 

[kjv1611]

I can think of 2 ways, maybe, but neither is automated. There may be an automated/semi-automated method out there that I'm not aware of.

1. Using MS Query to compare the sheets - though, I'm guessing you need to look at every field, every value, so not sure that will work as easily as I'm thinking.

2. VBA - I think this is how I would go if it were me.

Here's how I'd do it: (next post)..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

So if I were to compare 2 worksheets where I want to know what is different between the two, well, there are different ways. So based on what you mentioned, I suppose I'll go with creating a 3rd sheet as a copy of sheet1, and then highlighting the differences between it and Sheet2. That way, we're not changing the original data at all.

Well, this is a stab/start at it anyway... don't have time to keep digging, I thought I had it working, and it partially is.. got it highlighting with a light green, but not sure if it will get all the differences or not.. also, surely there's a way to make it faster.
[COCD]Sub CheckMySheets()
Dim wb As Workbook
Dim ws1 As Worksheet 'orig sheet1
Dim ws2 As Worksheet 'orig sheet2
Dim ws3 As Worksheet 'new worksheet
Dim ws4 As Worksheet 'new worksheet
Dim x As Long 'row
Dim y As Integer 'column
Dim v3 As Variant 'value from sheet3
Dim v4 As Variant 'value from sheet4

Set wb = ActiveWorkbook
Set ws1 = wb.Worksheets(1)
Set ws2 = wb.Worksheets(2)
ws1.Copy , ws2
Set ws3 = wb.Worksheets(ws2.Index + 1)
ws2.Copy , ws3
Set ws4 = wb.Worksheets(ws3.Index + 1)

ws3.Name = "ws1Compare"
ws4.Name = "ws2Compare"

ws3.Select
For y = 1 To ws3.Range("IV2").End(xlToLeft).Column
DoEvents
For x = 1 To ws3.Range("A65000").End(xlUp)
DoEvents
v3 = ws3.Cells(x, y)
ws4.Select
v4 = ws4.Cells(x, y)
Debug.Print v3 & " ---- " & v4
If v3 = v4 Then
'If same, do nothing
Else
'If different, highlight items
'ws3.Select
'ws3.Cells(x, y).Select
ws3.Select
ws3.Cells(x, y).Select
ws3.Cells(x, y).Interior.ColorIndex = 4
ws4.Select
ws4.Cells(x, y).Select
ws4.Cells(x, y).Interior.ColorIndex = 4
End If
Next x
Next y

If ws3 Is Nothing Then Else Set ws3 = Nothing
If ws2 Is Nothing Then Else Set ws2 = Nothing
If ws1 Is Nothing Then Else Set ws1 = Nothing
If wb Is Nothing Then Else Set wb = Nothing

End Sub[/CODE]

Feel free to give it a go with that if you want, edit, whatever..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

eeek! My code tags had a typo... should have previewed...

here I go again on the code:

Sub CheckMySheets()
    Dim wb As Workbook
    Dim ws1 As Worksheet 'orig sheet1
    Dim ws2 As Worksheet 'orig sheet2
    Dim ws3 As Worksheet 'new worksheet
    Dim ws4 As Worksheet 'new worksheet
    Dim x As Long 'row
    Dim y As Integer 'column
    Dim v3 As Variant 'value from sheet3
    Dim v4 As Variant 'value from sheet4
    
    Set wb = ActiveWorkbook
    Set ws1 = wb.Worksheets(1)
    Set ws2 = wb.Worksheets(2)
    ws1.Copy , ws2
    Set ws3 = wb.Worksheets(ws2.Index + 1)
    ws2.Copy , ws3
    Set ws4 = wb.Worksheets(ws3.Index + 1)
    
    ws3.Name = "ws1Compare"
    ws4.Name = "ws2Compare"
    
    ws3.Select
    For y = 1 To ws3.Range("IV2").End(xlToLeft).Column
        DoEvents
        For x = 1 To ws3.Range("A65000").End(xlUp)
            DoEvents
            v3 = ws3.Cells(x, y)
            ws4.Select
            v4 = ws4.Cells(x, y)
            Debug.Print v3 & " ---- " & v4
            If v3 = v4 Then
                'If same, do nothing
            Else
                'If different, highlight items
                'ws3.Select
                'ws3.Cells(x, y).Select
                ws3.Select
                ws3.Cells(x, y).Select
                ws3.Cells(x, y).Interior.ColorIndex = 4
                ws4.Select
                ws4.Cells(x, y).Select
                ws4.Cells(x, y).Interior.ColorIndex = 4
            End If
        Next x
    Next y
    
    If ws3 Is Nothing Then Else Set ws3 = Nothing
    If ws2 Is Nothing Then Else Set ws2 = Nothing
    If ws1 Is Nothing Then Else Set ws1 = Nothing
    If wb Is Nothing Then Else Set wb = Nothing
    
End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

hi,

and highlight where they differ

Differ in what respect? VALUES or FORMATS or BOTH or something else?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Hoaokapohaku]

As Skip pointed out, the solution depends a lot on what kind of differences you are looking for. If all you need to do is compare values, I'd make a third sheet and put the formula "=Sheet2!A1=Sheet3!A1" (note relative references) in every cell 729 x 98 cells. This will return a "TRUE" value in the cell if the values are the same, and "FALSE" if the are not. Then use the "Find" feature and search for "FALSE" and it will take you to each cell that has a differing value.

You could also automate this using VBA and make it give you a list of cells addresses with "FALSE" values. If you did it with VBA, you wouldn't neccesarily have to create the third sheet and formula as I mentioned above. You could just loop the cells in the range and compare values, but looping through all the cells would be much slower macro than if you had VBA create the third sheet, enter the formula in the entire range, "FIND" the cells where .value = "FALSE", then enter the addresses found on a separate sheet.