Good Day All
New to the forum, but relatively competenent with the basics of Excel. I will be asking a lot of questions as I try to expand my knowledge, so i thank you all in advance.
I need help to do this:
I am carrying out a review on a team of engineering staff and the qualifications budgeted against each position within the firm. I have about 100 positions and staff and each position can hold a number of (in various combinations) qualifications. Within these positions, there are about 30 different roles within the firm, and a differing number of staff hold these roles. A staff member only holds one role. The team is split into a Level and Sub-level (trade) structure (ranking and skill structure if you like).
The review is to ascertain qualification shortfalls in each position and staff member, and decide what qualifications are required for each position.
Therefore I have a simple table, as follows.
Column A (A3-A100) is a list of all the positions.
Column B (B3-B100) is a list of roles associated to that position
Column C (C3-C100) in the surname of the person in that position
Row 1 (D1-B120) is a list of all the qualifications in alternate columns, meaning there are two columns per qualification, one for what the position requires, and two, what the present incumbent holds. (I hope this makes sense).
I use a simple convention for completing the grid.
For the position details,
Y = Qualification is Required, and is currently attached to that position. (short for Yes)
R = Qualification is required but not attached to that position yet (short for Required)
N = Qualification is curently attached to that position, but not required anymore for that position (short for Not Required)
Blank = not required.
For the present incumbent details,
H = Incumbant holds this qualification (short for Held)
S = Used where the incumbent requires this qualification (there will be a Y or R in the preceding column), but does not hold it (short for Shortfall).
Worksheet 1 looks like:
this table goes on for 100 columns and 90 positions ish!
The first analysis is to ascertain the changes to the qualifications attached to each position. This has to be in a particular format on another worksheet, position in Column A, and Qualifications attached to each position in Column B. There are a varying number of qualifications per position, so the output needs to be dynamic as I do not want gaps as follows: (this will in effect be a form I will submit to the budget holders stating the changes I need to occur).
In addition, the qualification column needs formatting in a certain manner:
Qualification already attached to that position and requiring no change - black normal font.
Qualification to be attached to that position = Blue Bold (from the "R" in my naming convention above)
Qualification not required any more = Red Bold Font (from the "N) in my naming convention above)
From W/S1 above, I want to produce W/S 2 below
I suppose what the incumbant actually holds or not is rather a red herring - the data and budget is about the position and the qualifications attached to that position. It will produce a training burden output when a person is assigned to that position.
I appreciate this is a long post and probably have given too much nugatory information, but hopefully I have got everything across in a straightforward manner.
Many thanks in advance for your help!
Matt
New to the forum, but relatively competenent with the basics of Excel. I will be asking a lot of questions as I try to expand my knowledge, so i thank you all in advance.
I need help to do this:
I am carrying out a review on a team of engineering staff and the qualifications budgeted against each position within the firm. I have about 100 positions and staff and each position can hold a number of (in various combinations) qualifications. Within these positions, there are about 30 different roles within the firm, and a differing number of staff hold these roles. A staff member only holds one role. The team is split into a Level and Sub-level (trade) structure (ranking and skill structure if you like).
The review is to ascertain qualification shortfalls in each position and staff member, and decide what qualifications are required for each position.
Therefore I have a simple table, as follows.
Column A (A3-A100) is a list of all the positions.
Column B (B3-B100) is a list of roles associated to that position
Column C (C3-C100) in the surname of the person in that position
Row 1 (D1-B120) is a list of all the qualifications in alternate columns, meaning there are two columns per qualification, one for what the position requires, and two, what the present incumbent holds. (I hope this makes sense).
I use a simple convention for completing the grid.
For the position details,
Y = Qualification is Required, and is currently attached to that position. (short for Yes)
R = Qualification is required but not attached to that position yet (short for Required)
N = Qualification is curently attached to that position, but not required anymore for that position (short for Not Required)
Blank = not required.
For the present incumbent details,
H = Incumbant holds this qualification (short for Held)
S = Used where the incumbent requires this qualification (there will be a Y or R in the preceding column), but does not hold it (short for Shortfall).
Worksheet 1 looks like:
| Q1 | Q2 | Q3 | ||||||
| Pos | Role | Name | Y/R/N | H/S | Y/R/N | H/S | Y/R/N | H/S |
| Pos1 | Role1 | Name1 | Y | H | Y | H | R | S |
| Pos2 | Role2 | Name2 | Y | S | Y | H | ||
| Pos3 | Role3 | Name3 | Y | H | N | R | H | |
| Pos4 | Role4 | Name4 | Y | S | Y | H |
this table goes on for 100 columns and 90 positions ish!
The first analysis is to ascertain the changes to the qualifications attached to each position. This has to be in a particular format on another worksheet, position in Column A, and Qualifications attached to each position in Column B. There are a varying number of qualifications per position, so the output needs to be dynamic as I do not want gaps as follows: (this will in effect be a form I will submit to the budget holders stating the changes I need to occur).
In addition, the qualification column needs formatting in a certain manner:
Qualification already attached to that position and requiring no change - black normal font.
Qualification to be attached to that position = Blue Bold (from the "R" in my naming convention above)
Qualification not required any more = Red Bold Font (from the "N) in my naming convention above)
From W/S1 above, I want to produce W/S 2 below
| Position | Name | Qualification |
| Pos1 | Name1 | Q1 |
| Q2 | ||
| Q3 | ||
| Pos2 | Name2 | Q1 |
| Q2 | ||
| Pos3 | Name3 | Q1 |
| Q2 | ||
| Q3 | ||
| Pos4 | Name4 | Q2 |
| Q3 | ||
I suppose what the incumbant actually holds or not is rather a red herring - the data and budget is about the position and the qualifications attached to that position. It will produce a training burden output when a person is assigned to that position.
I appreciate this is a long post and probably have given too much nugatory information, but hopefully I have got everything across in a straightforward manner.
Many thanks in advance for your help!
Matt