Comparing two arrays what the best most efficient way

Hi welchs101, the following is an example of comparing two String Arrays.

Sub CompareArrays()
    Dim strArray1() As String, strArray2() As String
    Dim aCnt As Long
    ReDim strArray1(5): ReDim strArray2(5)
    strArray1(1) = "Will"
    strArray1(2) = "this"
    strArray1(3) = "work"
    strArray1(4) = "?"
    strArray1(5) = "I don't know!"
    
    strArray2(1) = "Will"
    strArray2(2) = "this"
    strArray2(3) = "work"
    strArray2(4) = ""
    strArray2(5) = "I don't know!"
    
    For aCnt = 1 To 5
        If strArray1(aCnt) = strArray2(aCnt) Then
            Debug.Print "Do Something"
        Else
            Debug.Print "Do Nothing"
        End If
    Next aCnt
End Sub

No problem! And?

Mordred, I think the implication is that the Arrays need not be of the same dimension and/or items in the same order ... ie "work" may be item 2 in strArray2.

welchs101, you can either iterate the 2nd Array for each iteration of the 1st exiting upon match, e.g:

Dim lngA1 As Long, lngA2 As Long
Dim avArray1 As Variant, avArray2 As Variant
'...arrays populated - assume sourced from Range and 2 dimensional
For lngA1 = LBound(avArray1,1) To UBound(avArray1,1) Step 1
    For lngA2 = LBound(avArray2,1) To UBound(avArray2,1) Step 1
        If UCase(avArray1(lngA1,1)) = UCase(avArray2(lngA2,1)) Then
            'do something
            Exit For
        End If
    Next lngA2
Next lngA1

alternatively apply a Match function (binary given sort) to array2 (use Index if multi dimensional - assume given subject matter that case is irrelevant):

Dim lngA1 As Long
Dim avArray1 As Variant, avArray2 As Variant, vMatch As Variant
'...arrays populated - assume sourced from Range and 2 dimensional
For lngA1 = LBound(avArray1,1) To UBound(avArray1,1) Step 1
    vMatch = Application.Match(avArray1(lngA1,1),Application.Index(avArray2,0,1))
    If IsNumeric(vMatch) Then
        If UCase(avArray2(vMatch,1)) = UCase(avArray1(lngA1,1)) Then
            'do something
        End If
    End If
Next lngA1

Given the speed of Arrays I suspect iteration would be quicker than the Match approach.

The fact that the arrays are sorted is helpful and with thought may permit some restrictions within the iteration, however, given the potential for missing items in either array it might not be as simple as appears at first glance.