# Compare two tables on different spreadsheets for differences



## Musto85 (Dec 1, 2022)

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 (Dec 1, 2022)

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 (Dec 2, 2022)

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 (Dec 22, 2022)

Up!


----------

