1

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.

tony
  • 267
  • 2
  • 11
  • 23
  • 1
    AFAIK, there is no direct way to do this for 2D arrays. So you'll really have to loop through the elements of the array and compare them. – L42 May 06 '15 at 03:40

2 Answers2

3

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
Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • Thanks! This solutin does work. Just had one clarification that I should of made. Using the arrays defined as you have for MyArr1 and MyArr2 it works, but my arrays are actually 2d arrays that were intialized as: `MyArr1 = Worksheets(1).Range("a1:f5").Value` - in this case seems that every row matches regardless of content in range fo cells? – tony May 06 '15 at 10:07
  • Update on error. seems to be a **Run Time error 13: type mismatch** that is occuring at the if condition when comparing : If Join(Application.Transpose(Application...)) Also I am only reading in around 100 rows. Saw some other posts mention it could have limitations for large number of rows but dont think that's the issue in my case – tony May 06 '15 at 12:04
  • Well found that my issue was that one particular cell seemed to contain too much text. Deleted that cell as It was not needed for my compare, and seemed to work. – tony May 06 '15 at 15:41
  • Excellent news, glad we could help :). – Dan Donoghue May 06 '15 at 22:29
2

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Transpose of course, I was trying to do this and couldn't get it working :) – Dan Donoghue May 06 '15 at 04:31
  • 1
    I just keep Transposing until it comes out right ;-) – Tim Williams May 06 '15 at 04:33
  • thanks for input on this. Have any suggestions on dealing with **Run Time error 13: type mismatch** which is occuring at Application.Index()? (Array only contains around 100 rows) – tony May 06 '15 at 12:11
  • If you can update your question with the code which is giving you problems it would be easier to suggest something. Without that I'd guess you have declared a variable as a wrong type. – Tim Williams May 06 '15 at 14:46