Vba Fastest Way Compare Two Lists Of Numbers
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:
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?
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
- → How do I create an array from a single form input box with php on octobercms?
- → Print the output value of an array in twig [OctoberCMS]
- → Declare an array variable to the controller/component [Laravel, October CMS]
- → search array without duplicates React.js
- → Populating array with items from different HTML attributes
- → Get all index value of 1 from binary "01000111"
- → Remove objects from array - Two different approaches, two different results when consulting the length of each array
- → Compare values in two arrays
- → adding multiple polygons in google maps using loops and arrays
- → .split() JS not "splitting" correctly?
- → Vue.js - Binding radio elements of same name to array