Compare two tables on different spreadsheets for differences

[Musto85]

Hi all,

The code below compares a row in wb1 with another row in wb2 for differences and returns a msgbox with OK or NOT OK.

Instead of comparing just one row I would like to compare the rest of the two tables with range of A2:F300, is this possible with an adaptation of the below code please?

Thanks!

Sub Compare()

   Dim test1 As Variant, test2 As Variant
   Dim wb1 As Workbook, wb2 As Workbook
   Dim destSht As Worksheet, shSet As Worksheet

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("H:\Project\Structure\Table\Staff List.xlsb")
Set destSht = wb2.Worksheets("Staff_List")
Set shSet = wb1.Worksheets("Table")

destSht.Unprotect "password1"
   
   test1 = Join(Application.Index(destSht.Range("A2:F2").Value, 1, 0), "|")
   test2 = Join(Application.Index(shSet.Range("A2:F2").Value, 1, 0), "|")
   
   If test1 = test2 Then
      MsgBox "OK"
   Else
      MsgBox "Not OK"
   End If
       
destSht.Protect "password1"

destSht.Parent.Close True

ThisWorkbook.Activate

End Sub

 

[mmhill]

Nice trick using JOIN and INDEX functions together. Didn't know about that one.

Based on your comparison above, if there is any difference in a row the test will fail. So, every cell on one sheet must be the same as it's counterpart on the other sheet. So, we could do 299 row comparisons or 5 column comparisons and get the same result. 5 is faster than 299.

The strComp function below does a NOT case sensitive comparison. If you want case sensitive, change vbTextCompare to vbBinaryCompare.

Changes below include:
** put ranges into arrays and compared the arrays (ay1 and ay2) ... This is much faster than working with sheets/ranges/cells
** transpose the array to look at columns rather than rows ... now you have a max of 5 column tests rather than 300 row tests
** if we fail 1 test, we Exit For as the result will be a NOT OK ... no sense in continuing the tests
** We start with strMsg = "" and make it "Not " if we fail a test. So, the message box says either ("" & "OK") or ("Not " & "OK") ... simple
** The range address is at the top so it's easy to change ... You could just put the address into the code.

Sub Compare()

    Const strRangeAddress As String = "A2:F300"

    Dim shSet As Worksheet, wb2 As Workbook, destSht As Worksheet
    Dim ay1 As Variant, ay2 As Variant, i As Integer, strMsg As String
  
    Set shSet = ThisWorkbook.Worksheets("Table")
    Set wb2 = Workbooks.Open("H:\Project\Structure\Table\Staff List.xlsb")
    Set destSht = wb2.Worksheets("Staff_List")
    
    destSht.Unprotect "password1"

        With Application
        
            ay1 = .Transpose(shSet.Range(strRangeAddress))
            ay2 = .Transpose(destSht.Range(strRangeAddress))
        
            For i = LBound(ay1, 1) To UBound(ay1, 1)
                If StrComp(Join(.Index(ay1, i, 0), "|"), Join(.Index(ay2, i, 0), "|"), vbTextCompare) <> 0 Then strMsg = "Not ": Exit For
            Next i
            
        End With
        
        MsgBox strMsg & "OK", vbOKOnly + vbInformation, "Finished Compare"
           
    destSht.Protect "password1"
    destSht.Parent.Close True
    
    ThisWorkbook.Activate

End Sub

 

[Musto85]

Thanks mmhill, I've had to add Dim wb1 and set wb1 as thisworkbook and it worked.

Do you believe a msgbox detailing in which row the difference lies or...just the data which is different could be added in the code above?

 

[Musto85]

Up!