Training Needs Analysis

[Morpheus2022]

Hi

I am currently working for an important client with a large number of staff.
I am trying to conduct a training needs analysis and need a formula to complete the following rapidly rather than trying to do this manually which would take many hours.
I'm sure its pretty strait forward but I might be overthinking it.

SCENARIO

SHEET 1 - ANALYSIS
REF: B1 = Course Name 1 C1=Course Name 2 D1=Course Name 3

REF: COL A
A2 = Employee 1
A3 = Employee 2
A4 = Employee 3

SHEET 2 - MASTER DATA

REF: A1 "Employee Name" B1 = "Course Name"
Employee 1 Course Name 1
Employee 2 Course Name 1
Employee 3 Course Name 2
Employee 1 Course Name 2
Employee 1 Course Name 3

WHAT I AM LOOKING FOR:
SHEET 1 FORMULA - In cell B2 =IF(the content of A2 is found in sheet two Employee Name column AND the content of sheet 1 B1 Course Name is found in Sheet 2 column B. Then Return "YES", Else "Blank")
The results in sheet one should show a row of Y's next to an employees name under the relevant course names where the learner is needed to attend a programme of learning.

Thanks.

 

[Kerryx]

you could just convert sheet Master data to a pivot table

Book1
	A	B	C	D
1	Employee	Cname
2	Emp1	Cname1
3	Emp2	Cname2
4	Emp5	Cname3
5	Emp4	Cname1
6	Emp1	Cname2
7	Emp2	Cname3
8	Emp2	Cname1
9	Emp5	Cname1
10
11	Count of Cname	Column Labels
12	Row Labels	Cname1	Cname2	Cname3
13	Emp1	1	1
14	Emp2	1	1	1
15	Emp4	1
16	Emp5	1		1
Master data

Copy and paste as values back to your Analysis sheet replace all the "1" with a "Y", filter out any missing employees if you need and voila.

Book1
	F	G	H	I	J
1	Employee	CnameY	Cname2	Cname3	Is employee on Analysis Sht
2	Emp1	Y	Y		Emp1
3	Emp2	Y	Y	Y
4	Emp4	Y			Emp4
5	Emp5	Y		Y	Emp5
Analysis

Cell Formulas
Range	Formula
J2:J5	J2	=IFNA(VLOOKUP(F2,$A$2:$A$5,1,FALSE),"")

 

[Morpheus2022]

Hi Kerry,

Not sure if I have explained myself all that well.
The cell values under CNAME would be blank.

If the Name EMP1 & the CName are found on the analysis spreadsheet are match in a row record return the value 1 or Y.
When I have attempted what you have outlined I get the name only.

Am I doing something wrong here? Sorry.

 

[Kerryx]

On the analysis sheet would you have employees and/or courses listed that would not be in the Master Data sheet?
The Master Data sheet contains list of employees and courses they must do, this is the data i moved to the Pivot table format.

I know that the original data on the Analysis sheet is similar to the layout below is that correct?

Book1
	A	B	C	D
1	Employee	Cname1	Cname2	Cname3
2	Emp1
3	Emp3
4	Emp5
5	Emp4
Analysis

It is the master data i originally converted to pivot table as i assume these are the courses the employees are meant to attend, assuming all the courses here are available.

 

[Kerryx]

If it only the employees and courses in the analysis sheet you need to run the data for this is the approach i would use, add 2 columns in Master data sheet to check against Analysis sheet
Then use these clumns AnyEmp and AnyCN as the source data for your matrix.

Book1
	A	B	C	D
1	Employee	Cname1	Cname2	Cname3
2	Emp1
3	Emp3
4	Emp5
5	Emp4
Analysis

Book1
	A	B	C	D	E	F	G	H	I
1	Employee	Cname	AnyEmp	AnyCN		Count of AnyCN
2	Emp1	Cname1	Emp1	Cname1			Cname1	Cname2	Cname3
3	Emp2	Cname2		Cname2			1	1	1
4	Emp5	Cname3	Emp5	Cname3		Emp1	1	1
5	Emp4	Cname1	Emp4	Cname1		Emp4	1
6	Emp1	Cname2	Emp1	Cname2		Emp5	1		1
7	Emp2	Cname3		Cname3
8	Emp2	Cname1		Cname1
9	Emp5	Cname1	Emp5	Cname1
Master data

Cell Formulas
Range	Formula
C2:C9	C2	=IFNA(VLOOKUP(A2,Analysis!$A$2:$A$5,1,FALSE),"")
D2:D9	D2	=HLOOKUP(B2,Analysis!$B$1:$D$1,1,FALSE)

 

[Morpheus2022]

You are amazing! Thank you so much Kerry. I knew there was a simple way around it. I really cannot thank you enough.

 

[Kerryx]

no problem glad to help