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

 

[Rick Rothstein]

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.

I should have mentioned... like spaces around commas, leading and trailing spaces are assumed to be part of the text being searched for... if the user puts a space in any of the text, it is assumed he/she meant that to be part of the text.

 

[Peter_SSs]

I'm generally more interested in efficiency than compactness & I must admit I haven't done any speed tests but it seems more sensible to me to not bother to check all remaining Source terms (there could be a lot I assume) once you find one that isn't in Text. So I would more likely go for something like ..

Function CheckEm(sText As String, sSource As String) As Long
  Dim V As Variant
  
  CheckEm = 1
  For Each V In Split(sSource, ",")
    If InStr("," & sText & ",", "," & V & ",") = 0 Then
      CheckEm = 0
      Exit Function
    End If
  Next V
End Function

.. however, in the spirit of fun, would this count as more compact?

Function AllThere(sText As String, sSource As String) As Long
  Dim V As Variant
  AllThere = 1
  For Each V In Split(sSource, ",")
    AllThere = -(InStr("," & sText & ",", "," & V & ",") > 0) * AllThere
  Next V
End Function

Note also that both my functions return 1 if sSource is empty. I'm not sure what the 'right' return would be for that.

 

[Rick Rothstein]

...but it seems more sensible to me to not bother to check all remaining Source terms (there could be a lot I assume) once you find one that isn't in Text.

Good idea!

So I would more likely go for something like ..

Function CheckEm(sText As String, sSource As String) As Long
  Dim V As Variant
  
  CheckEm = 1
  For Each V In Split(sSource, ",")
    If InStr("," & sText & ",", "," & V & ",") = 0 Then
      CheckEm = 0
      Exit Function
    End If
  Next V
End Function

You can compact your code further (without sacrificing efficiency) by moving the CheckEm=1 line of code to the bottom and taking advantage of the fact that you did ...

Function CheckEm(sText As String, sSource As String) As Long
  Dim V As Variant
  For Each V In Split(sSource, ",")
    If InStr("," & sText & ",", "," & V & ",") = 0 Then Exit Function
  Next
  CheckEm = 1
End Function

And if you wanted your code to return 0 instead of 1 when the source (Column B) cell is empty...

Function CheckEm(sText As String, sSource As String) As Long
  Dim V As Variant
  For Each V In Split(sSource, ",")
    If InStr("," & sText & ",", "," & V & ",") = 0 Then Exit Function
  Next
  CheckEm = -(Len(sSource) > 0)
End Function

 

[jahmaira]

Hey Guys!

Not sure what is going on but THANK YOU VERY MUCH!

I tried some of the functions you gave me and they all work just fine for what I need!

Thank you again, I really appreciate it

Have a nice day!

jem

 

[mikerickson]

Here is a formula based solution.

Either, select a cell and define names

Name: aWords
RefersTo: =TRIM(MID(SUBSTITUTE(Sheet1!$A1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255))

Name: bWords
RefersTo:=TRIM(MID(SUBSTITUTE(Sheet1!$B1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255))

And use the formula =ISNUMBER(SUMPRODUCT(MATCH(bWords,aWords,0)))

OR
use the CSE formula
{=ISNUMBER(SUMPRODUCT(MATCH(TRIM(MID(SUBSTITUTE(Sheet1!$B1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255)), TRIM(MID(SUBSTITUTE(Sheet1!$A1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255)), 0)))}

Unknown
	A	B	C	D	E	F
1	AP,HU,SU,WI	AP,HU		TRUE		TRUE
2	BE,ZD,ZO,AP,HU	BE,ZO,ZD		TRUE		TRUE
3	IR,OL,WI,MA,CA	IR,OL,NI,GW		FALSE		FALSE
4	EM,SU,AP,HU	EM,SU,MA,CA,AP,HU		FALSE		FALSE
Sheet1

Cell Formulas
Range	Formula
D1	=ISNUMBER(SUMPRODUCT(MATCH(bWords,aWords,0)))
F1	{=ISNUMBER(SUMPRODUCT(MATCH(TRIM(MID(SUBSTITUTE(Sheet1!$B1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255)), TRIM(MID(SUBSTITUTE(Sheet1!$A1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255)), 0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
aWords	=TRIM(MID(SUBSTITUTE(Sheet1!$A1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255))
bWords	=TRIM(MID(SUBSTITUTE(Sheet1!$B1,",",REPT(" ",255)),1+255*(COLUMN(Sheet1!$A:$AC)-1),255))

 

[RickXL]

Not sure what is going on but THANK YOU VERY MUCH!

Please don't mind us. If all the posters here lived in the same country Excel might become a contact sport.

If you have a solution that is the main thing.

Regards,

 

[RickXL]

Here is a formula based solution.

There is always one, isn't there?

Seriously though, full marks for ingenuity.

Regards,

 

[jahmaira]

I don't want to take advantage of your kindness, but I may have another question related to this one.

It would be possible to adjust the formula to have as a result the text value or values that are missing from column A?

here an example:

column A column B column C
AP,HU,IR AP,HU,LU LU
IR,OL,DJ

 

[jahmaira]

Sorry I posted it without finishing the example, hope it was enough clear thought, in others words I would like to have in column C the text values that are in column B but not in column A.

Thank you in advance!

jem

oh ok I just discovered the advanced button, here again an example:

A	B	C
PO,PG,IR,NI	NI,PG,OL	OL
PA,MA,CA,HU	MA,CA,HU,AP,NI	AP,NI
IR,OL,DJ	ZO,ZD,	ZO,ZD,BE

<tbody>
</tbody>

 

[Rick Rothstein]

It would be possible to adjust the formula to have as a result the text value or values that are missing from column A?

here an example:

column A column B column C
AP,HU,IR AP,HU,LU LU
IR,OL,DJ

I'll let Mike tackle that one given it is his formula, but using the code Peter posted in Message #12 as a basis, here is a UDF which will return a comma delimited list of missing source text...

Function Missing(sText As String, sSource As String) As String
  Dim V As Variant
  For Each V In Split(sSource, ",")
    If InStr("," & sText & ",", "," & V & ",") = 0 Then Missing = Missing & "," & V
  Next
  Missing = Mid(Missing, 2)
  If Right(Missing, 1) = "," Then Missing = Left(Missing, Len(Missing) - 1)
End Function

The UDF takes the same arguments as the previous UDF; for example...

=Missing(A1,B1)