# List/Return Difference Between 2 Cells Containing Comma Separated Strings

Back to: Excel Custom Function/Formulas

Suppose you have 2 Columns that has cells with comma separated values in them. E.G;

A1=Cat,dog,1,10,house

B1=Dog,mouse,11,10,home

You need to use 2 more cells and return a comma separated list of the difference. E.G:

C1: =GetDiffs1(A1,B1) which would return "cat,1,house" as neither of these values exist in B1

D1: =GetDiffs2(A1,B1) which would return "mouse,11,home" as neither of these values exist in A1

Now simply use the Fill Handle to copy down

## Custom Function 1

```Function GetDiffs1(Cell1 As  Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long

Array1 =  Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On  Error Resume Next
With WorksheetFunction
For lLoop = 0 To UBound(Array1)
strDiff = vbNullString
strDiff = .Index(Array2, 1, .Match(Array1(lLoop), Array2, 0))
If strDiff = vbNullString Then
lCheck = 0
lCheck = .Match(Array1(lLoop), Array2, 0)

If lCheck = 0 Then
strDiffs = strDiffs & "," & Array1(lLoop)
End If
End If

Next lLoop
End With

GetDiffs1 = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function```

## Custom Function 2

```Function GetDiffs2(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long

Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
For lLoop = 0 To UBound(Array2)
strDiff = vbNullString
strDiff = .Index(Array1, 1, .Match(Array2(lLoop), Array1, 0))
If strDiff = vbNullString Then
lCheck = 0
lCheck = .Match(Array2(lLoop), Array1, 0)

If lCheck = 0 Then
strDiffs = strDiffs & "," & Array2(lLoop)
End If
End If

Next lLoop
End With

GetDiffs2 = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function ```

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.