Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet

[smcquaid92]

I need to compare two sheets (Sheet 1 titled Client, Sheet 2 titled Advisor), each with a table with identical column headers and first column. The first column has the property names, and the additional columns have the information Fields about those properties.

I would like the Macro to output a table in a third sheet with four columns to show any differences but ignores cells that match: 1. Property, 2. Field, 3. Client, 4. Advisor as seen below:

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings


Try the following macro.
I added 2 more concepts, if the data in column A of the Client sheet does not exist in the Advisor sheet and vice versa. If the same data in column A always exists in both sheets, then it does not affect.

Sub showing_differences()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim f As Range
  Dim i As Long, j As Long, k As Long
  
  Set sh1 = Sheets("Client")
  Set sh2 = Sheets("Advisor")
  
  a = sh1.Range("A1:E" & sh1.Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1) * 4, 1 To 4)
  
  For i = 2 To UBound(a, 1)
    Set f = sh2.Range("A:A").Find(a(i, 1), , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      For j = 2 To UBound(a, 2)
        If a(i, j) <> f.Offset(0, j - 1) Then
          k = k + 1
          b(k, 1) = a(i, 1)
          b(k, 2) = a(1, j)
          b(k, 3) = a(i, j)
          b(k, 4) = f.Offset(0, j - 1)
        End If
      Next
    Else
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 4) = "does not exist"
    End If
  Next
  
  a = sh2.Range("A1:E" & sh2.Range("A" & Rows.Count).End(3).Row).Value
  For i = 2 To UBound(a, 1)
    Set f = sh1.Range("A:A").Find(a(i, 1), , xlValues, xlWhole, , , False)
    If f Is Nothing Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 3) = "does not exist"
    End If
  Next
  
  With Sheets("Differences")
    .Range("A2:D" & Rows.Count).ClearContents
    .Range("A2").Resize(k, 4).Value = b
  End With
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --