How to ***** if a list of comma separated text values is a subset of another list of comma separated text values

[jahmaira]

Hello!

This is my first question, I hope someone can help me and I hope I will be clear in explaining myself.

So I have two columns , A and B, in each row I have a list of text values separated by ",". What I would like to know is whether all the elements present in B are also present in A.

It will be brilliant to have something similar to: IF(ISNUMBER(SEARCH(B1;A1));1;0) but instead of having a single value to look for in B1 I have a list. I cannot use {("item1","item2","item3")} because the list of elements in both A and B change for every row.

here an example of my data set:


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]AP,HU,SU,WI[/TD]
[TD]AP,HU[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BE,ZD,ZO,AP,HU
[/TD]
[TD]BE,ZO,ZD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]IR,OL,WI,MA,CA[/TD]
[TD]IR,OL,NI,GW[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]EM,SU,AP,HU[/TD]
[TD]EM,SU,MA,CA,AP,HU[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I'm using excel for mac 2011

Thank you very much in advance to anyone who will take time to help me :D

 

[mikerickson]

I can't think of a formula based solution, but for VBA, this UDF and the formula =RemoveSubStrings(B1, A1, ",")

Function RemoveSubStrings(aString As String, DelimitedSubStrings As String, Optional Delimiter As String = " ") As String
    Dim oneSubString As Variant
    
    RemoveSubStrings = Delimiter & aString & Delimiter
    
    For Each oneSubString In Split(Delimiter & DelimitedSubStrings & Delimiter, Delimiter)
        RemoveSubStrings = Replace(RemoveSubStrings, Delimiter & oneSubString & Delimiter, Delimiter)
    Next oneSubString
    
    RemoveSubStrings = Left(RemoveSubStrings, Len(RemoveSubStrings) - Len(Delimiter))
    RemoveSubStrings = Mid(RemoveSubStrings, Len(Delimiter) + 1)
End Function