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

DaveMacdonald

New Member
Joined
Nov 28, 2013
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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.

NameIDCertification
Jones, Jacob111-1111cert1,cert5,cert9,cert30
Tyler, Jake222-2222cert5,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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 28, 2013
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGH
1NameIDCertification
2Jones, Jacob111-1111cert1,cert5,cert9,cert30cert5Jones, Jacob
3Tyler, Jake222-2222cert5,cert6, cert19Tyler, Jake
4
Lists
Cell Formulas
RangeFormula
H2:H3H2=FILTER(A2:A3,ISNUMBER(SEARCH(G2&",",C2:C3&",")))
Dynamic array formulas.
 

Forum statistics

Threads
1,147,632
Messages
5,742,230
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top