How to compare 2 cells with delimited items in each and output the difference in items

Question

What is the most effective technique to compare two Excel cells that each include delimited items?

As an illustration, I want to make sure that every hue in Set A is present in Set B and vice versa. The ideal solution would be to report which item from Set A is missing in Set B in a distinct column, say Result Set A. similarly, the output which item from Set B is missing in Set A in the Result Set B column.

Example

Colors Set A	Colors Set B
red;blue;green	yellow;purple;red
white;green	yellow;red

Goal

Colors Set A	Colors Set B	Result Set A	Result Set B
red;blue;green	yellow;purple;red	blue;green	yellow;purple
white;green;red	yellow;red	white;green	yellow

Note: As of right now, I do this very manually: Using excel Text to Columns with a delimiter, then doing COUNTIF to check if each cell exists in the opposite range - very complicated for larger data sets.

Answer 1

The following function would do this for you.

Function compare(setA As String, setB As String)

    Const delim As String = ";"
    
    For Each strPartA In Split(setA, delim)
        found = False
        For Each strPartB In Split(setB, delim)
            If Trim(strPartA) = Trim(strPartB) Then found = True
        Next
        If found = False Then compare = compare & strPartA & delim
    Next
    
    If Len(compare) > 0 Then compare = Left(compare, Len(compare) - 1)
    
End Function

You can use this as a UDF - i.e. enter it as a formula, e.g. if A1 contains "red;blue;green" and B1 contains "yellow;purple;red" then you can enter this formula in C1:

=compare(A1,B1)             to return "blue;green"

And this in D1

=compare(B1,A1)             to return "yellow;purple"

Or alternatively, you could write this into a routine:

Sub test()
    Dim c As Range
    With ActiveSheet
        For Each c In Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Cells
            c.Offset(, 2) = compare(c.Value, c.Offset(, 1).Value)
            c.Offset(, 3) = compare(c.Offset(, 1).Value, c.Value)
        Next
    End With
End Sub