Return a list of names (Column A) who have a specific text in any column of the same row (D:AH)

[DaveMacdonald]

This is a pretty specific question so I had a hard time finding good information on it. I would greatly appreciate anyone that can spare some time and knowledge.

I receive a generated report every month or so. I cant change the way it is generated but I can make changes after it is exportet It basically comes out with Column A (names) B (identifying number for Employee) and C, which is a comma separated list of deficiencies that individual has, in no particular order. The deficiencies are either for expired, or never issues certifications, of which everyone is required to have 34. The report looks something like the below example.

Name	ID	Certification
Jones, Jacob	111-1111	cert1,cert5,cert9,cert30
Tyler, Jake	222-2222	cert5,cert6, cert19

I could organize those manually, but this is for several hundred individuals, and the cert list is all mixed up. I separate the Cert column using Text to Columns and using the comma, thats no problem, but I still have to go in painful detail, sorting who has what. What I would like to do is have a way in a separate sheet, to select a particular certification, and have excel generate a list of names who have that cert listed, anywhere in their row.

So search for Cert5 returns both of these names in a decending list.

Is that possible? Or is there a better way to just organize it right off the bat?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DaveMacdonald]

Thanks for the advice! I'm using Officer 365 Version. I updated it in my profile.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Name	ID	Certification
2	Jones, Jacob	111-1111	cert1,cert5,cert9,cert30				cert5	Jones, Jacob
3	Tyler, Jake	222-2222	cert5,cert6, cert19					Tyler, Jake
4
Lists

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(A2:A3,ISNUMBER(SEARCH(G2&",",C2:C3&",")))
Dynamic array formulas.