CODEWITHSUNDEEP
arraysvbaexcel
tony
tony

Reputation: 277

Comparing 2-dimension arrays in VB Excel

I'm trying to compare two 2d arrays in VBA Excel.

Source:

1 2 3 4

4 5 6 2

3 3 4 4

Target:

4 5 3 2

1 2 3 4

3 7 7 5

Given the above two 2-d arrays which I will call source and target I want to compare each row from source with entire target and check if it exists in target. For Example row 1 from source (1 2 3 4) would be considered a match as it would found in target (at row 2). So I need to compare each row in target for a given row from source. If row in source does not exist in target then I will need to make note of this some how in order to mark as not existing in target.

Something on the lines of (not actual code just idea):

For i to ubound(srcArray)
    isFound = False
    For j To ubound(trgArray)
        If srcArray(i) = trgArray(j) Then
            isFound = True

    If Not isFound Then
        //make note of some sort

I know approach worked ok for single dim. array. But trying to do this for 2d arrays in some sort of loop in VB or other method. Not too familiar with VB in Excel. I would also like to look at each row as entire array if possible rather than comparing each element for each array individually.

Upvotes: 1

Views: 6562

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6216

Here is an example of how to loop and compare the elements of a 2D array:

Sub ArrayCompare()
Dim MyArr1 As Variant, MyArr2 As Variant, X as long, Y as long
MyArr1 = [{1,2,3,4;4,5,6,2;3,3,4,4}]: MyArr2 = [{4,5,3,2;1,2,3,4;3,7,7,5}]
For X = LBound(MyArr1) To UBound(MyArr1)
    For Y = LBound(MyArr1, 1) To UBound(MyArr1, 1)
        If MyArr1(X, Y) = MyArr2(X, Y) Then MsgBox X & ":" & Y & ":" & MyArr1(X, Y)
    Next
Next
End Sub

Here is my updated code to compare each row as a string (Thanks @Tim Williams :)):

Sub ArrayCompare()
Dim MyArr1 As Variant, MyArr2 As Variant, X As Long, Y As Long
MyArr1 = [{1,2,3,4;4,5,6,2;3,3,4,4}]: MyArr2 = [{4,5,3,2;1,2,3,4;3,7,7,5}]
For X = LBound(MyArr1) To UBound(MyArr1)
    For Y = LBound(MyArr2) To UBound(MyArr2)
        If Join(Application.Transpose(Application.Transpose(Application.Index(MyArr1, X, 0))), "|") = Join(Application.Transpose(Application.Transpose(Application.Index(MyArr2, Y, 0))), "|") Then MsgBox "Found a match at MyArr1 index:" & X & " and MyArr2 index:" & Y
    Next
Next
End Sub

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166825

If you really want to avoid loops then you use this approach to extract a single "row" out of your 2-d array for comparison purposes, but it might be faster to loop.

Sub Tester()

    Dim arr, rw

    arr = Range("A1:J10").Value 'get 2-d array from worksheet

    'get a 1-d array "row" out of the 2-d array
    rw = Application.Transpose( _
         Application.Transpose(Application.Index(arr, 1, 0)))

    'then you can (eg) create a string for comparison purposes
    Debug.Print Join(rw, Chr(0))

End Sub

Upvotes: 2

Related Questions