Extracting differences between two comma-separated strings

[NewOrderFac33]

Good afternoon - I have two columns of comma-separated two character codes (for example):

A                                                           B
AA,DB,LN,WE,UR,TD,QW                         AA,LN,WE,VF,WE,UR,OW,ZL

The the codes will always comprise two characters, but will not necessarily be in alphabetical order.

In the next column, I need to create a comma-separated string comprising all the two digit codes from the first list that do NOT appear in the second.

So, in the above example, the third column will contain DB,TD

Do any of you fine folk have any suggestions of the best way in which I can accomplish this?

Thanks in advance

Pete

 

[jim may]

I would perform your task in a Macro (VBA). by first Converting your cell A1 and B1 into two arrays using VBA's SPLIT() function;
then loop thru arr1 and arr2 -- Posting the difference in C1

 

[Fluff]

How about

Function GetUnique(a As String, b As String) As String
   Dim i As Long
   Dim Sp As Variant, tmp As Variant
   
   Sp = Split(b, ",")
   tmp = Split(a, ",")
   For i = 0 To UBound(Sp)
      tmp = Filter(tmp, Sp(i), False, vbTextCompare)
   Next i
   GetUnique = join(tmp, ",")
End Function

Used like
=GetUnique(A3,B3)

 

[Joe4]

I created my own function to do that. Just paste this code in VBA, and then you can use it like any other Excle function:

Function MyCompare(list1 As String, list2 As String) As String

    Dim arr1 As Variant
    Dim arr2 As Variant
    Dim i As Integer
    Dim j As Integer
    Dim found As Boolean
    Dim result As String
    
'   Turn strings into an arrays
    arr1 = Split(list1, ",")
    arr2 = Split(list2, ",")
    
'   Loop through all values in arr1
    For i = LBound(arr1) To UBound(arr1)
'       Set found to false to start
        found = False
'       Loop through values in arr2
        For j = LBound(arr2) To UBound(arr2)
'           See if values are equal
            If arr1(i) = arr2(j) Then
'               If they are, mark as found and exit for
                found = True
                Exit For
            End If
        Next j
'       If it gets through all values in arr2 without being found, add to list
        If found = False Then
            result = result & arr1(i) & ","
        End If
    Next

'   Write final result to string to return
    If Len(result) > 0 Then
        MyCompare = Left(result, Len(result) - 1)
    End If
    
End Function

So, if your values are in cells A2 and B2, and you want the results in cell C2, enter this formula in cell C2:
=MyCompare(A2,B2)


Edit: All that work, and Fluff posts a superior solution to mine a moment before I post!

 

[NewOrderFac33]

Thank you, Jim, Joe and Fluff - it's 22:00 in the UK now, so I'll give your solutions a try at work tomorrow and let you know hoe I get on.

Pete

 

[sandy666]

one way with PowerQuery (Get&Transform)

Load both cells as tables into PowerQuery Editor (suitable: TableLeft and TableRight)
then change layout to columns
merge them by LeftAnti
group and extract values as comma delimited string
remove unnecessary columns then load result to the sheet

btw. there are three elements, not two because of QW vs OW

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Left[/td][td=bgcolor:#000000]Right[/td][td][/td][td=bgcolor:#70AD47]Left[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AA,DB,LN,WE,UR,TD,QW[/td][td=bgcolor:#D9D9D9]AA,LN,WE,VF,WE,UR,OW,ZL[/td][td][/td][td=bgcolor:#E2EFDA]DB,TD,QW[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="TableLeft"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Left", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Left"),
    #"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter",{"Left"},Table5,{"Right"},"TableRight",JoinKind.LeftAnti),
    #"Grouped Rows" = Table.Group(#"Merged Queries", {"TableRight"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Left", each Table.Column([Count],"Left")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Left", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Left"})
in
    #"Removed Other Columns"[/SIZE]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="TableRight"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Right", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Right")
in
    #"Split Column by Delimiter"[/SIZE]

 

[NewOrderFac33]

Good moring, lovely people - thanks for all your help - I now have the solution to my requirements...
...unless anyone would be willing to come up with the OPPOSITE function i.e. one that returns a comma-separated string containing all the codes that are in BOTH strings..?

String 1 AB,VX,EX, DF,MX,IT,KJ,RQ,LP,RT,VB,LK,GW,HG,QY
String 2 AB,EX, DF,MX,IT,RQ,LP,RT,LK,GW,QY

Current function returns VX,KJ,VB,HG
New function should return AB,EX,DF,MX,IT,LP,RT,LK,GW

Thanks in advance

Pete

 

[NewOrderFac33]

Hi, Sandy - thanks for spotting my typo (my latest post should also show the new requirement returning QY too..!)
Unfortunately, I don't have PowerQuery and would be shot at dawn if I even THOUGHT about downloading it at work, but thanks for taking the time to look at my requirement!

 

[MickG]

Try this:-
Data in "A1" & "B1" results in "C1"

[COLOR="Navy"]Sub[/COLOR] MG01Mar33
[COLOR="Navy"]Dim[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Sp = Split([a1], ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR="Navy"]If[/COLOR] InStr([b1], Sp(n)) > 0 [COLOR="Navy"]Then[/COLOR]
        nStr = nStr & IIf(nStr = "", Sp(n), "," & Sp(n))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[c1] = nStr
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[NewOrderFac33]

MickG - that's brilliant, even though I'm still trying to figure out how it works! I also need to convert it into a function, if you're feeling particularly charitable this afternoon (he asked, in a whiny voice), although I WILL give it a go myself, too.
Regards Pete