Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects. There can only be upto 6 tasks per project.
| Project Name | Task1 | Task2 | Task3 | Task4 | Task5 | Task6 |
| Project1 | Painting | Chef | Gardener | |||
| Project2 | Tiling | Digging | Engineering | |||
| Project3 | Mechanic | Engineering | ||||
Here is a competency matrix showing the competencies of employees on different tasks. 1 indicates that the employee is competent to perform that task.
| Task | Tom | Jane | Mary | Paddy | Lynda |
| Painting | 1 | 1 | 1 | 1 | 1 |
| Tiling | 1 | 1 | 1 | 1 | 1 |
| Plastering | 1 | 1 | 1 | 1 | 1 |
| Digging | 1 | 0 | 1 | 1 | 1 |
| Mechanic | 1 | 1 | 1 | 0 | 1 |
| Detective | 1 | 1 | 1 | 1 | 1 |
| Engineering | 1 | 1 | 0 | 1 | 1 |
| Boxer | 1 | 0 | 1 | 1 | 1 |
| Chef | 1 | 1 | 1 | 1 | 1 |
| Gardener | 1 | 1 | 0 | 1 | 1 |
| Banker | 1 | 1 | 1 | 1 | 0 |
From these two tables, one may want to generate another table showing which employees can be assigned to which project (only those employees should be assigned to a project who can complete all tasks). So the ideal solution is to create another column (8th column) in the Project matrix table above which should have a drop down (Data > Data Validation) for every project showing which employees are competent for that project.
Here’s an illustration:
Assuming that the Project matrix is in range A1:G4 (headers are in row 1)
- In cell H2 (for Project1), the drop down should show Jane, Lynda, Paddy and Tom. Mary should not appear there because she cannot perform one of the 3 tasks required to complete the project i.e. Gardener.
- In cell H3 (for Project2), the drop down should show Lynda, Paddy and Tom. Jane and Mary should not appear there because they cannot perform the Digging and Engineering tasks respectively.
The solution is dynamic for the following:
- Projects added to the Project matrix Table; and
- Tasks added (upto 6 only) or edited in the Project matric Table; and
- Employees added to the Competency matrix Table; and
- Tasks added to the Competency matrix Table
I have solved this problem by using:
- Power Query; and
- Formulas in Data > Data Validation.
You may download my solution workbook from here or here.
Generate a list of assignees for different projects based on a competency matrix
{ 4 Comments }