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:

A	B	C
AP,HU,SU,WI	AP,HU	1
BE,ZD,ZO,AP,HU	BE,ZO,ZD	1
IR,OL,WI,MA,CA	IR,OL,NI,GW	0
EM,SU,AP,HU	EM,SU,MA,CA,AP,HU	0

<tbody>
</tbody>

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