CODEWITHSUNDEEP
excelvbaexcel-formula
supermariowhan
supermariowhan

Reputation: 75

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

What is the most efficient way to compare 2 cells with delimited items in each cell in excel?

For example, I want to check if each color in Set A appears in Set B and vice versa. Ideally, I would like a separate column, say Result Set A - to output which item from Set A is missing in Set B. similarly, in another column called Result Set B - output which item from Set B is missing in Set A.

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 manual: Using excel Text to Columns with delimiter, then doing COUNTIF to check if each cell exist in the opposite range - very complicated for larger data sets.

Upvotes: 0

Views: 149

Answers (1)

CLR
CLR

Reputation: 12254

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

Upvotes: 2

Related Questions