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.

Feb 23, 2023 in Others 457 views

## 1 answer to this question.

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```
• 38,010 points

## How to search a file with a particular extension and a particulat string in its name?

You can easily find the file using ...READ MORE

## How to compare 2 comna separated strings in informatica?

A java transformation can be used to ...READ MORE

## Company will be able to quickly issue SIM to user and expected gain in volume is approximately 10 times as the manual process of verification is replaced with secure automated system

LifeTel Telecom is the latest entrant in the ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

## Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

## Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE