Data Analysis & Exploitation

[mattyn]

This is probably quite easy for an Excel expert, I hope!

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).

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).

Column A: Column B
Position Qualification
Position1: Qualification1
Qualification2
Qualification3
Position2: Qualification1
Qualification4
Position3: Qualification1
Qualification6

etc!

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)

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.

The second analysis I need to produce is what shortfalls each incumbent has, and therefore producing that training burden to get him qualified to carry out that role.

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!

 

[mattyn]

Good afternoon mate

Hopefully you are enjoying your weekend. The code works perfectly on your workbook I have downloaded, and when I copy it into the test book I have uploaded.

However, when I copy the code into the live book, and run it, it fails with the following error:

"Run-time error '9'

Sub-script out of range"

When clicking on debug, it takes me to this line highlighted:

varOut(n, m) = varData(i, 1)

Any ideas?

Thank you again for your efforts on this! I owe you some Scotch!

Matt

 

[L. Howard]

Can you post a link to the actual workbook that is giving the error?

Given this statement from your example workbook, seems all should work well.

The sheets Tracker and Request map the actual worksheets on the live workbook in names, cell references etc.

I suspect it is a sneaky little item that doesn't fit somewhere. A sheet name misspelled is a subscript out of range type error.

Howard

 

[mattyn]

https://www.dropbox.com/s/q0ehtu6qaevnrny/Live Test.xlsm?dl=0

These are the exact worksheets - though I have changed the sensitive information - I hope you understand.

You will see I have added a button to run the code.

With the columns populated as such, all works fine. Delete all the Ys and add some different data it randomly comes up with that error; from the random data inputting I have done, it appears I have to have either a R, Y or N in cell G111. Only then will the code work.

I can live with that issue - you have done so much so if you want to leave it I am more than happy.

Matt

 

[L. Howard]

Hi Matt,

I could not produce the error you mentioned using the most recent workbook you provided a linked to.

Here is another shot at it, using Auto-Entry and drop downs in the Q columns fields.

Seems to work pretty good, however there are some "rules of order" to follow. This is a bit sketchy and will take some trial and error to see what does and does not work.

May not be suited to your needs.

With both sheet "fields" clear you will need to start with Name 1 enter the Q column entries in order also.

Then Name 2, Name 3 and so on. Cannot skip a name.

There does seem to be some latitude with the Q column entries once a transpose of that position has been entered on Request sheet. You can go back to a former position and make some Q column entries and they will listed on Request sheet in the proper place.

I believe it is a matter of entering a name to a position, then all the Q columns selections.
Then do the next name and all the Q column selection and continue down the column C with names assigned to Positions and entering (selecting) the Q column entries.

Give it a go, may not work for you.


https://www.dropbox.com/s/xfptf5it4y6wsmp/Q Book Auto Enter.xlsm?dl=0

Howard

 

[L. Howard]

Hi again Matt,

Dump previous version, and try this.

Enter a Title, a Surname and a First name on any position and select the Q columns entries.

Skip about to various Positions and enter the three, title, surname & first and then the Q columns selections.

Revisit a previous Position, and add additional Q column entries.

Check results on Request sheet.

Now go back to Tracker and "woops, made a wrong entry/s" and delete a few "wrong entries" and recheck Request sheet for the deletions.

https://www.dropbox.com/s/jbo4zk2nn92hn8e/Q Book Auto Enter Revised Drop Box.xlsm?dl=0


Howard

 

[L. Howard]

Matt, here is the code installed in the workbook from the link in your post #33.


https://www.dropbox.com/s/7emlwkm7tyofnlz/Live Test Auto Transpose.xlsm?dl=0

Seems to work fine.

Howard

 

[mattyn]

Howard

thank you again for all the work. I have not had chance to look at the latest as have been really busy with work etc!

however I will do so tomorrow hopefully.... And I will let you know how it goes. I did not want you to think I was ignoring the later work you have done! It is absolutely brilliant!!

Matt