List/Return Difference Between 2 Cells Containing Comma Separated Strings

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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

Extract Differences Between 2 Comma Separated Range Arrays Macro

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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 special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS