Ultimate Excel VBA/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

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 

Extract Differences Between 2 Comma Separated Range Arrays Macro

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.



Instant Download and Money Back Guarantee on Most Software

Ultimate Excel VBA/ EXCEL DASHBOARD REPORTS

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates