See if cell value exists in a column of comma seperated values

[LawnmowerITGuy]

I have 2 sheets in my workbook. I am trying to determine if the serial number value from any given row in sheet 1 exits in a column of comma separated values in row Sheet2. I have no control over how the data is exported from either system. I've tried to find and I dont get any data. I've tried match with all 3 match types. If I do an exact match I get NA, if I do less than I get for the serial numbers but it's incorrect, and the same with less than. I'm not sure what the best way to get the data I want is.

On Sheet1 I have data that looks like this. It consits of a NodeName, RowID, Poller type and value. For each node there will be the same amount of Rows with a model poller and a serial poller. I've added an exists column that I want to put in a yes or no if the value exists

NodeName	Row_ID	Poller	Status	Exists
Node1	0	EX1Model	ex3400
Node1	1	EX1Model	ex3400
Node1	0	EX1Serial	NY123456789
Node1	1	EX1Serial	NY124589648

Sheet2 contains serial numbers. The amount of serial numbers in a row is determined but how many units I have for that node

JW123548958, JW987654321, JW654987123, JW852963741

LX528936174, LX15975346

HV367259841

NY123456789, NY124589648

TC852643197

XU789258631

 

[Domenic]

Enter the following formula in E2, and copy down:

=IF(OR(ISNUMBER(SEARCH(","&D2&",",","&SUBSTITUTE(Sheet2!$A$1:$A$6," ","")&","))),"Yes","No")

lawmoweritguy.xlsm
	A	B	C	D	E
1	NodeName	Row_ID	Poller	Status	Exists
2	Node1	0	EX1Model	ex3400	No
3	Node1	1	EX1Model	ex3400	No
4	Node1	0	EX1Serial	NY123456789	Yes
5	Node1	1	EX1Serial	NY124589648	Yes
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IF(OR(ISNUMBER(SEARCH(","&D2&",",","&SUBSTITUTE(Sheet2!$A$1:$A$6," ","")&","))),"Yes","No")

lawmoweritguy.xlsm
	A
1	JW123548958, JW987654321, JW654987123, JW852963741
2	LX528936174, LX15975346
3	HV367259841
4	NY123456789, NY124589648
5	TC852643197
6	XU789258631
Sheet2

Hope this helps!

 

[LawnmowerITGuy]

This worked great, thank you!