Best way to match in a string of data to multiple criteria

[mssbass]

Within my spreadsheet on Sheet1, I have health data which includes a column called HC Code where 1 or more codes exist separated by a comma (e.g. B123, B566, C404, etc.). On Sheet 2, I have a list of 32 codes. If any of these codes in my list on Sheet2 match any of the codes in the HC Code column, I need to filter those rows (or at least identify which rows have a match).

I'm assuming the best way to do this is to have my VBA create a formula in Sheet1 in which to code as "Y" if a match exists to any of the codes listed on Sheet2, then filter by that column. If so, what's the best formula to place into this column?

 

[snjpverma]

You can change the range as per your requirement.

See if below formula works. you can then filter by true or false

Book1
	F	G	H	I	J	K
4	JC2, FY3, UY9				JC2	True
5					FY3	True
6					UY9	True
7					JC3	False
8					FY8	False
9					UY10	False
Events Notices

Cell Formulas
Range	Formula
K4:K9	K4	=ISNUMBER(SEARCH(J4,$F$4))

 

[mssbass]

It's kinda the other way around -
Say F4 is the data on Sheet 1 and says JC3, AA4, UY10 (that should have a "Y") because it matches one of the codes in column J
Say F4 says AA4, BB3 (that should have a "N" because none of it's cases match the list in column J

 

[snjpverma]

You can first use the Text to columns tool to parse your data based on the commas.

Then

use the below formula and Confirm with control + shift + enter. Not just enter.

Book1
	F	G	H	I	J	K	L
4	JC2	FY3	UY9	True			JC4
5							FY4
6							UY9
7							JC3
8							FY8
9							UY10
Events Notices

Cell Formulas
Range	Formula
I4	I4	{=OR(COUNTIF(F4:H4,$L$4:$L$9))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[sandy666]

You can try Power Query

 

[snjpverma]

You can try Power Query

Hi Sandy666, could you recommend a good video tutorial for learning Power query systematically?
I too am eager to learn it.

 

[sandy666]

@snjpverma
I've never watched any video (except my own ) but I read a lot

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

and some more...