vba fastest way compare two lists of numbers-CodePudding

I am working in an MS-Access 2010 environment. I have two strings of numbers. I need to compare them and find the missing numbers in list 2. These lists can be very large (more than 100.000 numbers), so I need a fast method to compare them.

Let us say the lists are as follows:

L1: "1,2,4,5,6,8,9"

L2: "1,2,6,9"

So I need to find the numbers 4, 5 and 8. How can I do this most efficiently? I can put them in an array and them loop through both arrays, but I am afraid it will be very slow if both lists contain over 100.000 values.

Would a dictionary approach be more efficient? If so, how?

CodePudding user response：

Please, try the next code. It uses a dictionary and the two strings split in arrays. It needs only two iterations per each array, which should not take too much. The arrays content not necessary to be sorted. It returns in a third array, its content being Joined and returned in Immediate Window. Of course, it can be used as an array, depending on your need:

Sub testCompare1DArrays()
  Dim arr1, arr2, noMatch, i As Long, k As Long, dict2 As Object
  
    arr1 = Split("1,2,4,5,6,8,9", ",")
    arr2 = Split("1,2,6,9", ",")
    Set dict2 = CreateObject("scripting.dictionary")
    
    'place the array to be checked in the dictionary (as keys)
    For i = 0 To UBound(arr2)
        dict2(arr2(i)) = vbNullString
    Next i
    
    ReDim noMatch(UBound(arr1)) 'set the array to keep the processed result to surelly have space for all occurrences
    For i = 1 To UBound(arr1)
        If Not dict2.exists(arr1(i)) Then noMatch(k) = arr1(i): k = k   1 'buld the returned arry for elements not being found as keys
    Next i
    
    If k > 0 Then
        ReDim Preserve noMatch(k - 1) 'keep only loaded elements
        Debug.Print Join(noMatch, "|") 'return in Immediate Window (Ctrl   G)
    Else
        Debug.Print "All elements of arr2 exists in arr1..."
    End If
End Sub