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

 

[JLGWhiz]

VBA might be the simplest solution.

Sub compareLists()
Dim i As Long, j As Long, c As Object, colA As Variant, colB As Variant, neg As Long
    With ActiveSheet
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            colB = Split(.Cells(i, 2), ",")
            For j = LBound(colB) To UBound(colB)
                    If InStr(.Cells(i, 1), colB(j)) = 0 Then
                        neg = 1
                        Exit For
                    End If
            Next
                If neg = 1 Then
                    .Cells(i, 3) = 0
                Else
                    .Cells(i, 3) = 1
                    neg = 0
                End If
        Next
    End With
End Sub

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I see that JLGWhiz has beaten me to it but I have a slightly different macro solution:

Function myMatch(r1 As Range, r2 As Range) As Long
    Dim arr1  As Variant
    Dim arr2  As Variant
    Dim ele1  As Variant
    Dim ele2  As Variant
    Dim mCnt  As Long
    
    arr1 = Split(r1, ",")
    arr2 = Split(r2, ",")
    For Each ele1 In arr2
        For Each ele2 In arr1
            If Trim(ele1) = Trim(ele2) Then
                mCnt = mCnt + 1
                Exit For
            End If
        Next
    Next
    If mCnt = UBound(arr2) - LBound(arr2) + 1 Then myMatch = 1 Else myMatch = 0
End Function

I have created a User-Defined Function called myMatch. You paste the above code into a new Module then you can use the new function in the worksheet:

Excel 2013
	A	B	C
1	A	B	C
2	AP,HU,SU,WI	AP,HU	1
3	BE,ZD,ZO,AP,HU	BE,ZO,ZD	1
4	IR,OL,WI,MA,CA	IR,OL,NI,GW	0
5	EM,SU,AP,HU	EM,SU,MA,CA,AP,HU	0
Sheet3

Cell Formulas
Range	Formula
C2	=myMatch(A2,B2)
C3	=myMatch(A3,B3)
C4	=myMatch(A4,B4)
C5	=myMatch(A5,B5)

Regards,

 

[JLGWhiz]

Had a coding error in the first one.

Sub compareLists()
Dim i As Long, j As Long, c As Object, colA As Variant, colB As Variant, neg As Long
    With ActiveSheet
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            colB = Split(.Cells(i, 2), ",")
            For j = LBound(colB) To UBound(colB)
                    If InStr(.Cells(i, 1), colB(j)) = 0 Then
                        neg = 1
                        Exit For
                    End If
            Next
                If neg = 1 Then
                    .Cells(i, 3) = 0
                    neg = 0
                Else
                    .Cells(i, 3) = 1
                End If
        Next
    End With
End Sub

 

[Rick Rothstein]

Hi and welcome to the MrExcel Message Board.

I see that JLGWhiz has beaten me to it but I have a slightly different macro solution:

Function myMatch(r1 As Range, r2 As Range) As Long
    Dim arr1  As Variant
    Dim arr2  As Variant
    Dim ele1  As Variant
    Dim ele2  As Variant
    Dim mCnt  As Long
    
    arr1 = Split(r1, ",")
    arr2 = Split(r2, ",")
    For Each ele1 In arr2
        For Each ele2 In arr1
            If Trim(ele1) = Trim(ele2) Then
                mCnt = mCnt + 1
                Exit For
            End If
        Next
    Next
    If mCnt = UBound(arr2) - LBound(arr2) + 1 Then myMatch = 1 Else myMatch = 0
End Function

I have created a User-Defined Function called myMatch. You paste the above code into a new Module then you can use the new function in the worksheet

Here is another (slightly more compact) way to write your UDF...

Function Matches(Text As String, Source As String) As Long
  Dim Cnt As Long, V As Variant, Arr() As String
  For Each V In Split(Source, ",")
    Cnt = Cnt - (InStr(" " & Replace(Text, ",", " , ") & " ", " " & Trim(V) & " ") > 0)
  Next
  Matches = -(Cnt = Len(Source) - Len(Replace(Source, ",", "")) + 1)
End Function

Besides my renaming the function Matches, the only other difference between our two functions is that my function returns 0 when the second argument (Column B value) is blank (which I prefer) whereas your function returns 1.

 

[RickXL]

Here is another (slightly more compact) way to write your UDF...

And here's an even more compact way:

Function Matches(Text As String, Source As String) As Long
    Dim Cnt As Long, V As Variant
    For Each V In Split(Source, ",")
        Cnt = Cnt - (InStr(" " & Replace(Text, ",", " , ") & " ", " " & Trim(V) & " ") > 0)
    Next
    Matches = -(Cnt = Len(Source) - Len(Replace(Source, ",", "")) + 1)
End Function

No need to Dim Arr().
Note:This code is not reliable if the input strings contain blanks.

 

[Rick Rothstein]

And here's an even more compact way:

Function Matches(Text As String, Source As String) As Long
    Dim Cnt As Long, V As Variant
    For Each V In Split(Source, ",")
        Cnt = Cnt - (InStr(" " & Replace(Text, ",", " , ") & " ", " " & Trim(V) & " ") > 0)
    Next
    Matches = -(Cnt = Len(Source) - Len(Replace(Source, ",", "")) + 1)
End Function

No need to Dim Arr().

Heh-heh, yeah, I forgot to delete it from one of my earlier iterations of the UDF.

Note:This code is not reliable if the input strings contain blanks.

I am not so sure of that... can you post an example of two text strings where this is the case?

 

[RickXL]

I am not so sure of that... can you post an example of two text strings where this is the case?

For you, Rick, of course.

Excel 2013
	A	B	C	D
1	A	B	C
2	AP,HU,SU,WI	AP,HU,SU	1	1
3	AA BE,ZD,ZO,AP,HU	ZO,ZD,AA	0	1
4	IR,OL,WI,MA,CA	IR,OL,NI,GW	0	0
5	EM,SU,AP,HU	EM,SU,MA,CA,AP,HU	0	0
Sheet3

Cell Formulas
Range	Formula
C2	=myMatch(A2,B2)
C3	=myMatch(A3,B3)
C4	=myMatch(A4,B4)
C5	=myMatch(A5,B5)
D2	=matches(A2,B2)
D3	=matches(A3,B3)
D4	=matches(A4,B4)
D5	=matches(A5,B5)

Regards,

 

[Rick Rothstein]

For you, Rick, of course.

Excel 2013

	A	B	C	D
1	A	B	C
2	AP,HU,SU,WI	AP,HU,SU	1	1
3	AA BE,ZD,ZO,AP,HU	ZO,ZD,AA	0	1
4	IR,OL,WI,MA,CA	IR,OL,NI,GW	0	0
5	EM,SU,AP,HU	EM,SU,MA,CA,AP,HU	0	0

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Oh, that kind of space (embedded where part of the text next to it matches one of the text being searched for). The solution for that problem (in my code) would require the user to "mean it" when they place a space next to a comma... that is, such a space cannot be used to "neaten up" the display... a space next to a comma would automatically be part of the text being searched for.

[table="width: 500"]
[tr]
	[td]Function Matches(Text As String, Source As String) As Long
    Dim Cnt As Long, V As Variant
    For Each V In Split(Source, ",")
        Cnt = Cnt - (InStr(Chr(1) & Replace(Text, ",", Chr(1) & "," & Chr(1)) & Chr(1), Chr(1) & V & Chr(1)) > 0)
    Next
    Matches = -(Cnt = Len(Source) - Len(Replace(Source, ",", "")) + 1)
End Function[/td]
[/tr]
[/table]

 

[RickXL]

Rick, This is a test, isn't it?

You have omitted the Trim() function this time so strings in column B with trailing blanks are not located.