Difference between two 2D Arrays in VBA

[Pimo]

Hi!
I am trying to get the difference of two 2D Arrays in VBA. Please look at the sample workbook

I got the code that I thought will be working well but I am getting a type mismatch error.... on this line: coll.Add arr1(i, j), arr1(i, j)

Here is my code so far:

Sub Test()
Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant
Dim coll As Collection
Dim i As Long, j As Long

With Worksheets("Sheet2")
    LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
    arr1 = .Range("A1:C" & LastRowColumnA).Value
End With

With Worksheets("Sheet1")
    LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
    arr2 = .Range("A1:C" & LastRowColumnA).Value
End With
Set coll = New Collection
For i = LBound(arr1, 1) To UBound(arr1, 1)
    For j = LBound(arr1, 2) To UBound(arr1, 2)
        coll.Add arr1(i, j), arr1(i, j)
    Next j
Next i

For i = LBound(arr2, 1) To UBound(arr2, 1)
    For j = LBound(arr2, 2) To UBound(arr2, 2)
        On Error Resume Next
        coll.Add arr2(i, j), arr2(i, j)
        If Err.Number <> 0 Then
            coll.Remove arr2(i, j)
        End If
        On Error GoTo 0
   Next j
Next i

ReDim arr3(1 To coll.Count, 1 To 1)

For i = 1 To coll.Count
    arr3(i, 1) = coll(i)
    Debug.Print arr3(i, 1)
Next i

Worksheets("Sheet2").Range("F1").Resize(UBound(arr3, 1), 1).Value = arr3 End Sub

Does anyone knows how it can be solved?




arrays.xlsm

[snb]

What is the difference between the worksheet 'result' and the formulation 'this should be the output' ?

[Pimo]

What is the dirrerence between the worksheet 'result' and the formulation 'this should be the output' ?

I just wanted to have the output to be in the result sheet
'this should be the output' is the output I want to get

[snb]

Which criteria are being applied ?

[Jan Karel Pieterse]

The runtime error is due to the fact that the Key argument of the collection's add method must be of type String. SO you can avoid that problem using:

coll.Add arr1(i, j), CStr(arr1(i, j))

[Paul_Hossler]

Do all 3 columns have to match or are you just looking for adds or deletes based on (say) column A?

[p45cal]

I think you're looking for something like this:

Sub Test()
Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant
Dim coll As Collection
Dim i As Long, j As Long, LastRowColumnA As Long, zz As String, x

With Worksheets("Sheet1")
  LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
  arr1 = .Range("A1:C" & LastRowColumnA).Value
End With

With Worksheets("Sheet2")
  LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
  arr2 = .Range("A1:C" & LastRowColumnA).Value
End With
Set coll = New Collection
For i = LBound(arr1) To UBound(arr1)
  zz = ""
  For j = LBound(arr1, 2) To UBound(arr1, 2)
    zz = zz & "¬" & arr1(i, j)
  Next j
  coll.Add zz, zz
Next i

For i = LBound(arr2) To UBound(arr2)
  zz = ""
  For j = LBound(arr2, 2) To UBound(arr2, 2)
    zz = zz & "¬" & arr2(i, j)
  Next j
  On Error Resume Next

  coll.Add zz, zz
  If Err.Number <> 0 Then
    coll.Remove zz
  End If
  On Error GoTo 0
Next i

ReDim arr3(1 To coll.Count, 1 To 3)

For i = 1 To coll.Count
  x = Split(coll(i), "¬")
  For j = 1 To 3
    arr3(i, j) = x(j)
  Next j
Next i

With Worksheets("Sheet2").Range("F1").Resize(UBound(arr3), UBound(arr3, 2))
  .NumberFormat = "@"
  .Value = arr3
End With
End Sub

[p45cal]

In the attached is a Power Query solution (2 actually)
On Sheet2, there's table at cell G9, and another at cell K9. Right-click one of them and choose Refresh to update it.
They are the same, only one of them has all the steps in one query and the other uses multiple queries to get there.
I've added a test row to each source table for testing.

[Pimo]

p45cal


Thank you so much! This is what I've been looking for!<br>Works great!

[p45cal]

This is what I've been looking for

Which solution did you go for in the end?

[Pimo]

I went for VBA because it is a part of a bigger macro.
I will use the second solution for other things where I don't need to use macro