Intersect two dictionaries

Question

Let's say I have two dictionaries as following:

dict1 = {vessel1: [a, b, c], vessel2: [a, d, e], ...}

dict2 = {operation1: [a, d], operation2: [a, e, b], ...}

Each dictionary (dict1, dict2) is a dictionary of dictionaries, so that a, b, c, d, e, f and g are dictionaries too.

What I want is, for example, intersect dict1(vessel1) with dict2(operation2), and have a result dictionary as following:

result_dictionary_of_intersection = [a, b]

I.e., have a result dictionary that only contains items that both vessel1 and operation2 have.

Remembering: a and b are dictionaries too.

It's not clear to me what you mean by intersecting dictionaries. If you want to use a dictionary like a set, see: http://stackoverflow.com/questions/4749698/excel-vba-is-there-anything-like-javas-set-container-in-vba/4751101#4751101. Assuming [a,b,c] and [a,e,b] represent lists (or collections, arrays, whatever), and the a, b, etc. in each list are the same dictionary *instances*, that will work just fine. — jtolle, Mar 23 '11 at 16:04

Stewbob · Accepted Answer · 2011-03-23T15:53:45.043

This will return the a and b dictionaries as you intended. This assumes that the key for the vessel1 dictionary is the string "vessel1", and the key for the operation2 dictionary is "operation2". You can of course, replace those string literals with variables in the code.

Dim newDict As Dictionary
Set newDict = New Dictionary

For Each myKey In dict1("vessel1").Keys

   If dict2("operation2").Exists(myKey) Then
        newDict.Add myKey, dict2("operation2")(myKey)
   End If

Next

If you want a little more flexibility about what you are using for dict1 and dict2, you can do it this way (which actually makes the code a little more readable):

Set tmpDict1 = dict1("vessel1")
Set tmpDict2 = dict2("operation2")

Dim newDict As Dictionary
Set newDict = New Dictionary

For Each myKey In tmpDict1.Keys

   If tmpDict2.Exists(myKey) Then
        newDict.Add myKey, tmpDict2(myKey)
   End If

Next

I'd thought VBA would have an intersection builtin method, but I'm learning that even a simple sort list function is hard to obtain from VBA. — Gabriel L. Oliveira, Mar 23 '11 at 16:11

score 0 · Answer 2 · edited Mar 25 '13 at 16:42

You may find this helpful:

Sub testMe()
Dim dict1 As New Dictionary
Dim dict2 As New Dictionary
Dim dict3 As New Dictionary

Dim dictAll As New Dictionary
Dim dictUnion As New Dictionary
Dim dictTemp As New Dictionary

dict1.Add "A", "A"
dict1.Add "B", "B"
dict1.Add "C", "C"
dict1.Add "D", "D"
dict1.Add "E", "E"

dict2.Add "C", "C"
dict2.Add "D", "D"
dict2.Add "E", "E"
dict2.Add "F", "F"

dict3.Add "C", "C"
dict3.Add "D", "D"

dictAll.Add 1, dict1
dictAll.Add 2, dict2
dictAll.Add 3, dict3

Dim var As Variant
Dim i As Integer

Set dictUnion = dictAll(1)

For Each var In dictAll
    Set dictTemp = dictAll(var)
    Set dictUnion = intMe(dictUnion, dictTemp)
Next

'Set dictUnion = intMe(dict1, dict2)
For Each var In dictUnion
    Debug.Print var
Next

Set dict1 = Nothing
Set dict2 = Nothing
Set dict3 = Nothing
Set dictAll = Nothing
Set dictUnion = Nothing
Set dictTemp = Nothing

End Sub

Function intMe(dict1 As Dictionary, dict2 As Dictionary) As Dictionary
Dim dictInt As New Dictionary
Dim var As Variant
For Each var In dict1.Keys
    If dict2.Exists(var) Then
        dictInt.Add var, var
    End If
Next
Set intMe = dictInt
End Function

Sub Intersect(dAll As Dictionary)

Dim var As Variant
Dim subVar As Variant
Dim dict As Dictionary

For Each var In dAll

    If var <> "Account_LV0" And var <> "Account_LV1" Then


        Set dict = dAll(var)
        Debug.Print var & "|" & dict.Count
        'For Each subVar In dict.Keys
        '    Debug.Print subVar
        'Next
    End If
Next


End Sub

Intersect two dictionaries

2 Answers2