Category: [Excel]  Demo Available 

---

List/Return Difference Between 2 Cells Containing Comma Separated Strings

 

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 

 

See also:

Add Excel Worksheets in Month/Monthly Order

Add Excel Worksheets in Numeric Sequence

Add Worksheets to Excel via VBA

Find Feature to Find 3 Matching Criteria in 3 Columns in Excel

 

Free Training Course: Lesson 1 - Excel Fundamentals

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•