Split and compare concatenated strings in Excel

Question

Consider the following 2 cells with concatenated strings:

    +------------+------------+------------+------------+
    |     A      |     B      |      C           D      |
+-- +------------+------------+------------+------------+
| 1 | A, B, C, D |  A, B, E   |            |            |
+---+------------+------------+------------+------------+

I need to compare the values in A1 and B1, as follows:

• in C1: names that are in A1 but not in B1 (here: C, D)
• in D1: names that are in B1 but not in A1 (here: E

Names are always one word, and names are separated by , (comma-space).

So the desired output is:

    +------------+------------+------------+------------+
    |     A      |     B      |      C           D      |
+-- +------------+------------+------------+------------+
| 1 | A, B, C, D |  A, B, E   | C, D       | E          |
+---+------------+------------+------------+------------+

Is there a way in Excel to do this? Any help would be greatly appreciated!

Answer 1

This is a simpler approach, let me know if i misunderstand the problem:

• Split(rngRemove, ",") to make the loop and replace string if exist.
• replace all "," by " " (many spaces created will be treated with the trim command later)
• Loop and replace
• remove unnecessary spaces and replace them back with ","

Here's the code:

Function GetString(rng As Range, rngRemove As Range) As String
    Dim j: j = Split(rngRemove, ",")
    Dim str_first As String: str_first = Replace(rng, ",", " ")

        For Each Item In j
            str_first = Replace(str_first, Item, "")
        Next

    GetString = Replace(Trim(str_first), " ", ",")  
End Function

Answer 2

Delimited String Comparison

Option Explicit

Function Split2(String1 As String, String2 As String, _
  Optional First1Second2 As Long = 1, _
  Optional Unique As Boolean = True, _
  Optional SourceDelimiter As String = ", ", _
  Optional TargetDelimiter As String = ", ") As String

    Dim vnt1 As Variant   ' First Source Array
    Dim vnt2 As Variant   ' Second Source Array
    Dim vntT As Variant   ' Target Array
    Dim i1 As Long        ' First Array Elements Counter
    Dim i2 As Long        ' Second Array Elements Counter
    Dim iT As Long        ' Target Array Elements Counter
    Dim strW As String    ' Current Write String

    ' Split Strings to Arrays.
    vnt1 = Split(String1, SourceDelimiter)
    vnt2 = Split(String2, SourceDelimiter)
    iT = -1

    ' Check arguments to redirect to appropriate subroutine.
    If Unique Then
        Select Case First1Second2
            Case 1: GoSub Compare1
            Case 2: GoSub Compare2
            Case Else
        End Select
    Else: GoSub Compare3: End If

    ' Join values from Target Array to resulting string.
    If iT > -1 Then Split2 = Join(vntT, TargetDelimiter)

Exit Function

Compare1:
    ' Calculate unique values in First String.
    For i1 = 0 To UBound(vnt1)
        For i2 = 0 To UBound(vnt2)
            If vnt1(i1) = vnt2(i2) Then Exit For
        Next
        If i2 > UBound(vnt2) Then strW = vnt1(i1): GoSub WriteToTarget
    Next
    Return

Compare2:
    ' Calculate unique values in Second String.
    For i2 = 0 To UBound(vnt2)
        For i1 = 0 To UBound(vnt1)
            If vnt2(i2) = vnt1(i1) Then Exit For
        Next
        If i1 > UBound(vnt1) Then strW = vnt2(i2): GoSub WriteToTarget
    Next
    Return

Compare3:
    ' Calculate same values in both strings.
    For i1 = 0 To UBound(vnt1)
        For i2 = 0 To UBound(vnt2)
            If vnt1(i1) = vnt2(i2) Then strW = vnt1(i1): _
              GoSub WriteToTarget: Exit For
        Next
    Next
    Return

WriteToTarget:
    ' Write current Write String to Target Array.
    iT = iT + 1
    If iT > 0 Then
        ReDim Preserve vntT(iT)
    Else: ReDim vntT(0): End If
    vntT(iT) = strW
    Return

End Function

Answer 3

If you have Windows Excel 2010+, you can use Power Query with a custom column utilizing the List.RemoveMatchingItems function.

• Get the data with Get & Transform from table.

• Add two custom columns:

  • Formula for 1: List.RemoveMatchingItems(Text.Split([Column1],","),Text.Split([Column2],","))
  • Formula for 2: List.RemoveMatchingItems(Text.Split([Column2],","),Text.Split([Column1],","))

• Select the double headed arrow at the top of the new columns, and

  • extract values with a comma as the delimiter

M-Code

    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.RemoveMatchingItems(Text.Split([Column1],","),Text.Split([Column2],","))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each List.RemoveMatchingItems(Text.Split([Column2],","),Text.Split([Column1],","))),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values1"