Intersection of two arrays of Strings in VBA

Question

This question might be a bit similar to this question but it is not. Intersection of two arrays of ranges

Okay, I am currently working on a project with VBA and I need to filter a table. There are two filters looks like this (a1 and a2 contain strings for filter):

Dim a1() As String
Dim a2() As String

.Autofilter Field:=1, Criterial:=Array(a1)
.Autofilter Field:=1, Criterial:=Array(a2)

So as you notice, the 2nd line of autofilter just overwrites, in a way, the previous filter and this is not the way I would like the filter to behave. I would like to have an array to have mutual elements of a1 and a2 so that I can filter with the mutual elements of two arrays.

How would you intersect two arrays that contain couple of strings? I prefer not to use cells for this purpose just to reduce the worksheets on my workbook.

"Just do it". What prevents you from doing it? Find an algorithm, implement it, done. — user202729, Jan 31 '18 at 09:35
Alternatively you can duplicate column and filter 1st column by `a1` and 2nd column by `a2`. EDIT: What are arrays' bounds? Are they equal to each other? How do you populate arrays? From `.Range.Value` or with `Split()` or manualy? — AntiDrondert, Jan 31 '18 at 11:04

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Using dictionary:

Dim strDict As Object, i As Integer
Dim a1() As String, a2() As String
Dim strFilter As String
' ...
'Creating a dictionary
Set strDict = CreateObject("Scripting.Dictionary")
'I assumed both of the arrays start either from 1 or 0
'Populate dictionary with elements of one of the arrays
'Then check if elements from another array exist in dictionary
'And add them to a string
For i = Lbound(a2) To Ubound(a2)
    strDict(a2(i)) = a2(i)
Next
For i = Lbound(a1) To Ubound(a1)
    If strDict.Exists(a1(i)) Then strFilter = a1(i) & "," & strFilter
Next
'...
.Autofilter Field:=1, _
            Criterial:=Split(Left(strFilter, Len(strFilter) - 1), ","), _
            Operator:=xlFilterValues

Using dictionary save us from using nested loops, number of iterations will be Ubound(a1)+Ubound(a2) instead of Ubound(a1)*Ubound(a2), which is great, unless arrays are relatively small (<=2).

score 0 · Answer 2 · answered Jan 31 '18 at 09:49

You could run nested loops - the outer loop goes through one array, and the inner loop checks if the entry exists in the other array. You can either output to a 3rd array, or use ReDim to expand the first array and add items to the end

Dim aIntersect() As String
Dim iLoop1 As Integer, iLoop2 As Integer
Dim bTest As Boolean
Dim iOutput As Integer

'Copy everything from a1 to aIntersect
iOutput = LBound(aIntersect)
For iLoop1 = LBound(a1) To UBound(a1)
    aIntersect(iOutput) = a1(iLoop1)
    iOutput = iOutput + 1
Next iLoop1
'Add the missing items from a2
For iLoop2 = LBound(a2) To UBound(a2)
    bTest = True ' Reset test
    For iLoop1 = LBound(a1) To UBound(a1)
        If a1(iLoop1) = a2(iLoop2) Then
            bTest = False 'Already exists
            Exit For 'No need to test further
        End If
    Next iLoop1
    If bTest Then 'If it did not exists, insert it
        aIntersect(iOutput) = a2(iLoop1)
        iOutput = iOutput + 1
    End If
Next iLoop1
'aIntersect now contains everything from a1, and anything from a2 that was not already in a1

Intersection of two arrays of Strings in VBA

2 Answers2

Using dictionary: