Intersection and Union of 2 cells which are strings in the excel using VBA

Question

I have 2 cells with the contents "1 2 3" and "2 3 4", How do i find the intersection(2 3) and union(1 2 3 4) of these 2 cells and dump it in another cell. "1 2 3" and "2 3 4" are dumped by using the below guide.

you can refer this question

VLOOKUP with multiple criteria returning values in one cell

Answer 1

You can use FilterXML and XPath to get Union and Intersections

Union:

=TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A1&" "&A2," ","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"))

Intersection:

=TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A1&" "&A2," ","</s><s>")&"</s></t>","//s[preceding::*=.]"))

Answer 2

Union:

=TEXTJOIN(" ",,UNIQUE(FILTERXML("<t><s>"&SUBSTITUTE(A1&" "&A2," ","</s><s>")&"</s></t>","//s")))

Intersection:

=TEXTJOIN(" ",,MODE.MULT(FILTERXML("<t><s>"&SUBSTITUTE(A1&" "&A2," ","</s><s>")&"</s></t>","//s")))

---

NOTE

But ultimately, it would probably be easier to refer to the base data and not the outcome of another formula that needs to be parsed.

Answer 3

Union and Intersect (Math)

In Excel use it like this:

=DSU(A1,B1)
=DSI(A1,B1,,", ")

The Code

Option Explicit

' Union
Function DSU(ByVal String1 As String, _
             ByVal String2 As String, _
             Optional ByVal Delimiter As String = " ", _
             Optional ByVal JoinDelimiter As String = " ") As String
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim Data1D() As String
    Data1D = Split(String1, Delimiter): writeDict1 dict, Data1D
    Data1D = Split(String2, Delimiter): writeDict1 dict, Data1D
    writeDictToString DSU, dict, JoinDelimiter
End Function

' Intersect
Function DSI(ByVal String1 As String, _
             ByVal String2 As String, _
             Optional ByVal Delimiter As String = " ", _
             Optional ByVal JoinDelimiter As String = " ") As String
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim dict2 As Object: Set dict2 = CreateObject("Scripting.Dictionary")
    Dim Data1D() As String
    Data1D = Split(String1, Delimiter): writeDict1 dict, Data1D
    If isDictEmpty(dict) Then Exit Function
    Data1D = Split(String2, Delimiter): writeDict1 dict2, Data1D
    If isDictEmpty(dict2) Then Exit Function
    addDictCount dict, dict2
    If isDictEmpty(dict) Then Exit Function
    writeDictCountToString DSI, dict, JoinDelimiter
End Function

' Called by DSU, DSI
Sub writeDict1(ByRef dict As Object, Data1D() As String)
    Dim j As Long
    For j = LBound(Data1D) To UBound(Data1D)
        dict(Data1D(j)) = 1
    Next j
End Sub
' Called by DSU
Sub writeDictToString(ByRef ResultString As String, _
                      dict As Object, _
                      Optional ByVal JoinDelimiter As String = " ")
    If isDictEmpty(dict) Then Exit Sub
    Dim Key As Variant, j As Long
    For Each Key In dict.Keys
        ResultString = ResultString & JoinDelimiter & Key
    Next Key
    ResultString = Right(ResultString, Len(ResultString) - Len(JoinDelimiter))
End Sub
' Called by DSI
Sub addDictCount(ByRef dict As Object, dict2 As Object)
    Dim Key As Variant, j As Long
    For Each Key In dict2.Keys
        dict(Key) = dict(Key) + 1
    Next Key
End Sub
' Called by DSI
Sub writeDictCountToString(ByRef ResultString As String, _
                           dict As Object, _
                           Optional ByVal JoinDelimiter As String = " ")
    If isDictEmpty(dict) Then Exit Sub
    Dim Key As Variant, j As Long
    For Each Key In dict.Keys
        If dict(Key) > 1 Then
            ResultString = ResultString & JoinDelimiter & Key
        End If
    Next Key
    If ResultString <> "" Then ResultString _
      = Right(ResultString, Len(ResultString) - Len(JoinDelimiter))
End Sub
' Called by DSU, DSI, writeDictToString, writeDictCountToString
Function isDictEmpty(dict As Object) As Boolean
    If dict.Count = 0 Then isDictEmpty = True
End Function