Training Need Analysis

[Arshad Nadamal]

Good morning!

I work in the Training Dept of a large company. I am in the process of collecting Training Needs for the coming year. I am planning to send an Excel file to all supervisors with the list of their employees. Against each employee, I have given 5 columns for the supervisor to enter Training Courses or Programs that they would like to attend.

Once I receive this file from the supervisors I would like to consolidate them in one file. I would like all the Training courses (currently populated in the 5 columns) to appear in one column. In other words if an employee has been nominated for 3 programs, then I want his name to appear 3 times (in 3 rows). This will help me in analyzing which program is required by how many employees.

I am attaching the sample file. I would like to have some ideas from Excel experts.

Thanks
Arshad

 

[Chihiro]

There's couple of ways to do this.
1. VBA
2. PowerQuery

Here, I'll explain PowerQuery method (with minimal use of M).
1. First convert Data range in sample file to Table format (I assumed you only need visible range. So used F9:R27)
2. Use new workbook and import data using Get & Transform (i.e. PowerQuery) -> From FIle -> From Workbook
3. Don't load it to workbook but just create connection.
4. Reference the query (this will create new instance of the query).
5. Remove all but the first set of columns and filter out "Empty". Result looks like below. Load as connection only.

6. Now create another reference to first query. This time filter out "Course or Program" and "Querter" column sets 1 & 3 to 5. Leaving 2nd set only (keep first 3 columns intact). Filter out "Empty" and rename column set to match exactly with step #5. Load as connection only.
7. Repeat for column sets 3 to 5.
8. Right click on any of the reference query and choose Append.
9. Choose 3 or more tables and append all the reference query together.


The result can be loaded or kept as connection until you append other workbooks in same manner.



You can manually create the rest or Copy and Paste M formula found in each query created above to fresh one and just change the file/query name reference.