Set Operations

On This Page

Common set operations include intersection, union, difference, symmetric difference, subset, and superset operations. Python provides corresponding functions for direct calculation, while Excel VBA requires programming to compute them. When programming in Excel VBA, knowledge of writing functions is involved — please refer to Chapter 10.

Intersection Operation

For two given sets, the intersection operation finds the elements that are present in both sets.

【Excel VBA】

The following Intersection function is used to compute the intersection of two given sets. The two sets are represented by two one-dimensional arrays, and their intersection is represented by a one-dimensional array. To compute the intersection:

Create a dictionary using the elements of the first array as keys.

Iterate through the second array and use the dictionary’s Exists function to check if each element of the second array exists in the dictionary. If it exists, it is an element of the intersection and is added to a new one-dimensional array.

Finally, return this new one-dimensional array, which is the desired intersection.

Sample file path: Samples\ch09\Excel VBA\IntersectionOperation.xlsm.

code.vba 
Function Intersection(arr1(), arr2())
    Dim intI As Integer
    Dim intK As Integer
    Dim arr3()
    Dim d As Dictionary
    Set d = New Dictionary

    ' Create dictionary d using elements of the first array as keys
    For intI = LBound(arr1) To UBound(arr1)
        d(arr1(intI)) = ""
    Next

    intK = 0
    ' Iterate through the second array; if element exists in dictionary d, add to new array
    For intI = LBound(arr2) To UBound(arr2)
        If d.Exists(arr2(intI)) Then
            ReDim Preserve arr3(intK)
            arr3(intK) = arr2(intI)
            intK = intK + 1
        End If
    Next

    ' Return the new array (the intersection)
    Intersection = arr3
End Function
Sub Test()
    Dim arr1(3), arr2(2)
    Dim arr3()
    Dim intI As Integer

    arr1(0) = 9: arr1(1) = 1: arr1(2) = 8: arr1(3) = 12
    arr2(0) = 8: arr2(1) = 7: arr2(2) = 1

    ' Call Intersection function to compute intersection
    arr3 = Intersection(arr1, arr2)

    Debug.Print "Set 1: " & vbTab;
    For intI = LBound(arr1) To UBound(arr1)
        Debug.Print arr1(intI);
    Next
    Debug.Print

    Debug.Print "Set 2: " & vbTab;
    For intI = LBound(arr2) To UBound(arr2)
        Debug.Print arr2(intI);
    Next
    Debug.Print

    Debug.Print "Intersection: " & vbTab;
    For intI = LBound(arr3) To UBound(arr3)  ' Output elements of intersection
        Debug.Print arr3(intI);
    Next
End Sub

When running the procedure, the Immediate Window outputs the elements of the intersection:

code.vba 
Set 1:     9  1  8  12
Set 2:     8  7  1
Intersection:    8  1

【Python】

In Python, we can use the & operator or the intersection() method of a set object to compute the intersection of two given sets.

code.python 
>>> {9, 1, 8, 12} & {8, 7, 1}
{8, 1}
>>> {9, 1, 8, 12}.intersection({8, 7, 1})
{8, 1}

Thus, the intersection of two given sets is a new set composed of the elements common to both sets.

Union Operation

Putting the elements of two sets together and removing duplicates yields their union.

【Excel VBA】

The following Union function is used to compute the union of two given sets. The two sets are represented by two one-dimensional arrays, and their union is represented by a one-dimensional array. To compute the union:

Create a dictionary using the elements of both arrays as keys (since dictionary keys must be unique, this automatically removes duplicates).

Use the dictionary’s Keys method to retrieve all keys, which are returned as a one-dimensional array — this is the desired union.

Sample file path: Samples\ch09\Excel VBA\UnionOperation.xlsm.

code.vba 
Function Union(arr1(), arr2())
    Dim intI As Integer
    Dim d As Dictionary
    Set d = New Dictionary

    On Error Resume Next
    ' Create dictionary using elements of both arrays
    For intI = LBound(arr1) To UBound(arr1)
        d(arr1(intI)) = ""
    Next
    For intI = LBound(arr2) To UBound(arr2)
        d(arr2(intI)) = ""
    Next

    ' Use dictionary's Keys method to get all keys (the union)
    Union = d.Keys
End Function
Sub Test()
    Dim arr1(3), arr2(2)
    Dim arr3()
    Dim intI As Integer

    arr1(0) = 9: arr1(1) = 1: arr1(2) = 8: arr1(3) = 12
    arr2(0) = 5: arr2(1) = 7: arr2(2) = 1

    ' Compute union
    arr3 = Union(arr1, arr2)

    Debug.Print "Set 1: " & vbTab;
    For intI = LBound(arr1) To UBound(arr1)
        Debug.Print arr1(intI);
    Next
    Debug.Print

    Debug.Print "Set 2: " & vbTab;
    For intI = LBound(arr2) To UBound(arr2)
        Debug.Print arr2(intI);
    Next
    Debug.Print

    Debug.Print "Union: " & vbTab;
    For intI = LBound(arr3) To UBound(arr3)
        Debug.Print arr3(intI);
    Next
End Sub

When running the procedure, the Immediate Window outputs all elements of the union:

code.vba 
Set 1:     9  1  8  12
Set 2:     5  7  1
Union:     9  1  8  12  5  7

【Python】

We can use the | operator or the union() method of a set object to compute the union of two given sets.

code.python 
>>> {9, 1, 8, 12} | {8, 7, 1}
{1, 7, 8, 9, 12}
>>> {9, 1, 8, 12}.union({8, 7, 1})
{1, 7, 8, 9, 12}

Thus, the union of two given sets is a new set formed by combining all elements of both sets and removing duplicates.

Difference Operation

For two given sets

and

, the difference

is

minus the intersection of

and

; the difference

is

minus the intersection of

and

.

【Excel VBA】

The following Difference function is used to compute the difference of two given sets (in the order they are provided). The two sets are represented by two one-dimensional arrays, and their difference is represented by a one-dimensional array. To compute the difference:

Create a dictionary using the elements of the second array as keys.

Iterate through the first array; if an element does not exist in the dictionary, add it to a new array.

Finally, return the new array, which is the difference of the two sets.

Sample file path: Samples\ch09\Excel VBA\DifferenceOperation.xlsm.

code.vba 
Function Difference(arr1(), arr2())
    Dim intI As Integer
    Dim intK As Integer
    Dim arr3()
    Dim d As Dictionary
    Set d = New Dictionary

    ' Create dictionary using elements of the second array as keys
    For intI = LBound(arr2) To UBound(arr2)
        d(arr2(intI)) = ""
    Next

    intK = 0
    ' Iterate through the first array; if element not in dictionary, add to new array
    For intI = LBound(arr1) To UBound(arr1)
        If Not d.Exists(arr1(intI)) Then
            ReDim Preserve arr3(intK)
            arr3(intK) = arr1(intI)
            intK = intK + 1
        End If
    Next

    ' Return the new array
    Difference = arr3
End Function
Sub Test()
    Dim arr1(3), arr2(2)
    Dim arr3()
    Dim intI As Integer

    arr1(0) = 9: arr1(1) = 1: arr1(2) = 8: arr1(3) = 12
    arr2(0) = 8: arr2(1) = 7: arr2(2) = 1

    ' Compute difference: arr1 - arr2
    arr3 = Difference(arr1, arr2)

    Debug.Print "Set 1: " & vbTab;
    For intI = LBound(arr1) To UBound(arr1)
        Debug.Print arr1(intI);
    Next
    Debug.Print

    Debug.Print "Set 2: " & vbTab;
    For intI = LBound(arr2) To UBound(arr2)
        Debug.Print arr2(intI);
    Next
    Debug.Print

    Debug.Print "Difference(Set 1 - Set 2): " & vbTab;
    For intI = LBound(arr3) To UBound(arr3)
        Debug.Print arr3(intI);
    Next
    Debug.Print

    Debug.Print "Difference(Set 2 - Set 1): " & vbTab;
    ' Compute difference: arr2 - arr1
    arr3 = Difference(arr2, arr1)
    For intI = LBound(arr3) To UBound(arr3)
        Debug.Print arr3(intI);
    Next
End Sub

When running the procedure, the Immediate Window outputs the difference of Set 1 minus Set 2 and Set 2 minus Set 1:

code.vba 
Set 1:     9  1  8  12
Set 2:     8  7  1
Difference(Set 1 - Set 2):   9  12
Difference(Set 2 - Set 1):   7

【Python】

We can use the minus sign - or the difference() method of a set object to compute the difference of two given sets.

code.python 
>>> {9, 1, 8, 12} - {8, 7, 1}
{9, 12}
>>> {9, 1, 8, 12}.difference({1, 2, 5})
{9, 12}
>>> {8, 7, 1} - {9, 1, 8, 12}
{7}
>>> {8, 7, 1}.difference({9, 1, 8, 12})
{7}

Thus, the difference of two given sets is a new set obtained by subtracting their intersection from each set.

Symmetric Difference Operation

For two given sets, their symmetric difference is obtained by subtracting their intersection from their union.

【Excel VBA】

The following SymDif function is used to compute the symmetric difference of two given sets. The two sets are represented by two one-dimensional arrays, and their symmetric difference is represented by a one-dimensional array. The code first computes the intersection and union of the given sets, then computes the difference between the union and the intersection, which is the desired symmetric difference.

Sample file path: Samples\ch09\Excel VBA\SymmetricDifferenceOperation.xlsm.

code.vba 
Function SymDif(arr1(), arr2())
    Dim intI As Integer
    Dim intK As Integer
    Dim arr3(), arr4(), arr5()
    Dim d As Dictionary, d2 As Dictionary
    Set d = New Dictionary
    Set d2 = New Dictionary

    ' Compute intersection
    For intI = LBound(arr1) To UBound(arr1)
        d(arr1(intI)) = ""
    Next
    intK = 0
    For intI = LBound(arr2) To UBound(arr2)
        If d.Exists(arr2(intI)) Then
            ReDim Preserve arr3(intK)
            arr3(intK) = arr2(intI)
            d2(arr3(intK)) = ""
            intK = intK + 1
        End If
    Next

    ' Compute union
    For intI = LBound(arr2) To UBound(arr2)
        d(arr2(intI)) = ""
    Next
    arr4 = d.Keys

    ' Compute symmetric difference: union - intersection
    intK = 0
    For intI = LBound(arr4) To UBound(arr4)
        If Not d2.Exists(arr4(intI)) Then
            ReDim Preserve arr5(intK)
            arr5(intK) = arr4(intI)
            intK = intK + 1
        End If
    Next

    SymDif = arr5
End Function
Sub Test()
    Dim arr1(3), arr2(2)
    Dim arr3()
    Dim intI As Integer

    arr1(0) = 9: arr1(1) = 1: arr1(2) = 8: arr1(3) = 12
    arr2(0) = 8: arr2(1) = 7: arr2(2) = 1

    arr3 = SymDif(arr1, arr2)

    Debug.Print "Set 1: " & vbTab;
    For intI = LBound(arr1) To UBound(arr1)
        Debug.Print arr1(intI);
    Next
    Debug.Print

    Debug.Print "Set 2: " & vbTab;
    For intI = LBound(arr2) To UBound(arr2)
        Debug.Print arr2(intI);
    Next
    Debug.Print

    Debug.Print "Symmetric Difference: " & vbTab;
    For intI = LBound(arr3) To UBound(arr3)
        Debug.Print arr3(intI);
    Next
End Sub

When running the procedure, the Immediate Window outputs the symmetric difference of the given sets:

code.vba 
Set 1:     9  1  8  12
Set 2:     8  7  1
Symmetric Difference:    9  12  7

【Python】

We can use the ^ operator or the symmetric_difference() method of a set object to compute the symmetric difference of given sets.

code.python 
>>> {9, 1, 8, 12} ^ {8, 7, 1}
{7, 9, 12}
>>> {9, 1, 8, 12}.symmetric_difference({8, 7, 1})
{7, 9, 12}

The union of sets {9, 1, 8, 12} and {8, 7, 1} is {1, 7, 8, 9, 12}, and their intersection is {1, 8}. The symmetric difference equals the union minus the intersection, so it is {7, 9, 12}.

Subset and Superset Operations

For two given sets

and

, if set

is greater than or equal to set

, and all elements of

are in

, then set

is a subset of set

, and set

is a superset of set

.

【Excel VBA】

The following IsSubset function is used to determine if a given set arr2 is a subset of set arr1. The two sets are represented by two one-dimensional arrays. If arr2 is a subset of arr1, it returns True; otherwise, False.

Sample file path: Samples\ch09\Excel VBA\SubsetSupersetOperation.xlsm.

code.vba 
Function IsSubset(arr1(), arr2()) As Boolean
    ' arr1 >= arr2: determine if arr2 is a subset of arr1
    Dim intI As Integer
    Dim d As Dictionary
    Set d = New Dictionary

    ' Create dictionary using elements of the first array as keys
    For intI = LBound(arr1) To UBound(arr1)
        d(arr1(intI)) = ""
    Next

    IsSubset = True
    ' If arr2 is larger than arr1, return False
    If UBound(arr2) - LBound(arr2) > UBound(arr1) - LBound(arr1) Then
        IsSubset = False
    Else
        ' If all elements of arr2 are in the dictionary, return True
        For intI = LBound(arr2) To UBound(arr2)
            If Not d.Exists(arr2(intI)) Then
                IsSubset = False
                Exit Function
            End If
        Next
    End If
End Function
Sub Test()
    Dim arr1(3), arr2(2)
    Dim intI As Integer

    arr1(0) = 9: arr1(1) = 1: arr1(2) = 8: arr1(3) = 12
    arr2(0) = 8: arr2(1) = 9: arr2(2) = 1

    Debug.Print "Set 1: " & vbTab;
    For intI = LBound(arr1) To UBound(arr1)
        Debug.Print arr1(intI);
    Next
    Debug.Print

    Debug.Print "Set 2: " & vbTab;
    For intI = LBound(arr2) To UBound(arr2)
        Debug.Print arr2(intI);
    Next
    Debug.Print

    Debug.Print "Set 2 is a subset of Set 1: " & vbTab;
    Debug.Print IsSubset(arr1, arr2)  ' Subset judgment
End Sub

When running the procedure, the Immediate Window outputs the following result. The judgment result is True, indicating that Set 2 is a subset of Set 1.

code.vba 
Set 1:     9  1  8  12
Set 2:     8  9  1
Set 2 is a subset of Set 1:     True

【Python】

We can use the <= operator or the issubset() method of a set object for subset operations. For sets

and

, if

or A.issubset(B) returns True, then set

is a subset of set

.

code.python 
>>> {8, 9, 1} <= {9, 1, 8, 12}
True
>>> {8, 9, 1}.issubset({9, 1, 8, 12})
True

For sets

and

, if

, then set

is a proper subset of set

.

code.python 
>>> {8, 9, 1} < {9, 1, 8, 12}
True

For sets

and

, if

or A.issuperset(B) returns True, then set

is a superset of set

.

code.python 
>>> {9, 1, 8, 12} >= {8, 9, 1}
True
>>> {9, 1, 8, 12}.issuperset({8, 9, 1})
True

For sets

and

, if

, then set

is a proper superset of set

.

code.python 
>>> {9, 1, 8, 12} > {8, 9, 1}
True