Produce List of Names not Qualified due to Match Error

[MaintenanceReportGuy]

Sheet 1
Column A I have a list of Names and Column B I have their qualifications

Column A has multiples of the same name due to certain people having multiple qualifications

Sheet 2
I have an alpha roster of each person in the company

I need to find a way to produce a worksheet that shows each qualification and who from the alpha roster DOES NOT (not yelling lol) have that qualification. I’m thinking this is a Power BI or Access problem? I couldn’t figure it out in Excel how to produce names.

Thanks in advance for your time.

 

[Peter_SSs]

Could you use something like this?

E2, F2, etc shows the list of qualifications.
Below that "x" indicates that person does not have the qualification.

21 11 14.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Qual
2	Name 2	Q 2			Q 2	Q 4	Q 1	Q 5	Q 3
3	Name 5	Q 2		Name 1	x
4	Name 3	Q 4		Name 2		x	x
5	Name 4	Q 1		Name 3	x		x	x	x
6	Name 2	Q 5		Name 4	x	x		x
7	Name 1	Q 1		Name 5		x	x	x	x
8	Name 1	Q 3
9	Name 1	Q 4
10	Name 1	Q 1
11	Name 4	Q 1
12	Name 1	Q 4
13	Name 1	Q 1
14	Name 2	Q 2
15	Name 1	Q 4
16	Name 2	Q 3
17	Name 4	Q 3
18	Name 1	Q 5
19	Name 2	Q 3
20	Name 1	Q 5
Not qualified

Cell Formulas
Range	Formula
E2:J2	E2	=IFERROR(INDEX($B$2:$B$100,MATCH(0,INDEX(COUNTIF($D$2:D2,$B$2:$B$100)+($B2:$B100=""),0),0)),"")
E3:J7	E3	=IF(OR(E$2="",COUNTIFS($A$2:$A$20,$D3,$B$2:$B$20,E$2)),"","x")

 

[MaintenanceReportGuy]

I will give this a shot in the morning, Using the X method I can still build tables to send up, that’s a great idea! Thank you! ?

 

[Peter_SSs]

Using the X method I can still build tables to send up

I wasn't exactly sure what format of results you were looking for. Perhaps it is this? (If not you could post a sample so we knew what we were shooting for)

21 11 14.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Qual
2	Name 2	Q 2					Q 2	Q 4	Q 1	Q 5	Q 3
3	Name 5	Q 2		Name 1			Name 1	Name 5	Name 2	Name 3	Name 3
4	Name 3	Q 4		Name 2			Name 3		Name 3	Name 4	Name 5
5	Name 4	Q 1		Name 3			Name 4		Name 5	Name 5
6	Name 2	Q 5		Name 4
7	Name 1	Q 1		Name 5
8	Name 1	Q 3
9	Name 1	Q 4
10	Name 2	Q 3
11	Name 4	Q 3
12	Name 2	Q 4
13	Name 4	Q 4
14	Name 1	Q 5
Not qualified (2)

Cell Formulas
Range	Formula
G2:K2	G2	=IFERROR(INDEX($B$2:$B$14,MATCH(0,INDEX(COUNTIF($F$2:F2,$B$2:$B$14)+($B2:$B14=""),0),0)),"")
G3:K7	G3	=IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW($D$3:$D$7)/(COUNTIFS($A$2:$A$14,$D$3:$D$7,$B$2:$B$14,G$2)=0),ROWS(G$3:G3))),"")

 

[MaintenanceReportGuy]

With the X method, I can simply filter each EIC and look at the names on the left to see who needs it. This will work for what I needed on my report, it would of even worked with just who had it. I just didn't think to do it this way. Thanks so much!

 

[Peter_SSs]

You're welcome. Glad you have something that works for you.