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!

 

[L. Howard]

Matt,

Here is a two sheet demo using the data example you posted.

Click the blue lightning bolt and see results on sheet4.

https://www.dropbox.com/s/ux22q97bph4aq5s/Q Reformat and Color Code v1.xlsm?dl=0

Howard

 

[mattyn]

Howard

Thank you. I am away with work for a couple of days so will try to get some connectivity and test! Failing that I will be home Thursday so will try then.

I really appreciate your efforts!!

Matt

 

[L. Howard]

Okay, see ya then.

Howard

 

[mattyn]

Howard

Thank you so much for the efforts in this.

I have been having a play with it. All seems to work brilliantly with the example I sent you. I have set up the worksheet example (so have not changed any sheet names etc) so it has a lot more qualifications and positions. The "Transpose" works effortlessly - (i did delete the "format" command at the end of that code as I was getting an error). The error appears in the "format" code -

Run-time error '13':

Type Mis-match

Clicking de-bug takes me to the line

Case "R"

Do you have any idea what causes this? When using the example spreadsheet with just the entries I sent you, it is all fine, it only appears when I add more data to both columns and rows.

Thank you again - it is a cool piece of work!

Matt

 

[mattyn]

I think I have found it - I have altered the line

Set myRng = .Range("A1:I" & LRow)

and

Set myQ = .Range("A1:I1")

to cover the new range of the columns, and it works!

Is there any way that can alter automatically?

 

[mattyn]

Howard

Please ignore my last question. Here is the tricky bit (well for me anyway), and I apologise if I should have mentioned this in the original question!

Comparing the Test Workbook to the Proper Workbook:

Sheet 1 is called Tracker
Sheet 4 is called Request

Sheet 1 - The first Position Number is at A3 (header at A2): Tracker - The first Position Number is at A9 (header at A8)

Sheet 1 - The Qualifications are all on Row 1 (starting at D1): Tracker - The Qualifications are all on Row 5 (starting at G5 and going to HB5)

These are now the only two columns I need to transfer. I do not need to transfer names as it is irrelevant to the request form!

Sheet 4 - The Position Numbers start at A2, Names at B2, Qualifications at C2: On Request, can I have the Position number start at A61, with the Qualifications at M61? I need no other columns filled. I will not need headers on the Request sheet as I can have those already populated. Does this make sense?

This will allow the Request Form to have all the mandatory stuff it needs. Obviously "Format" will need to concentrate on Rows M61 onwards.

I just cannot quite see where you state on the "Transpose" code the cells for the data to appear! Hope this makes sense!

Thank you again. I hope it is only small adjustments.

Matt

 

[L. Howard]

Hi Matt,

How about posting a link to an "exact" sample workbook, with Sheets Tracker and Request.

Include the restating of what gets transferred and what does not.

And brief, but representative example, on Request sheet as to how it should look. Enough to show a couple examples of all three color codes, RED, BLUE & NONE.

The TRANSPOSE you ask about is done with the RESIZE in these two lines. A simple transpose does not work because the varHeader and varOut are arrays and must copied to a like sized range. Where a simple copy of a range can be to a single cell, the upper-left-most of the new destination range.

Sheets("Sheet4").Range("A1").Resize(, 3) = varHeader
Sheets("Sheet4").Range("A2").Resize(myCnt, 3) = varOut

Howard

 

[mattyn]

As requested Howard, using Dropbox as you use that:

https://www.dropbox.com/s/ntrp04cz8z7zi3v/Test Book Howard.xlsx?dl=0

Hopefully you can download and see it!

|Many thanks

Matt

 

[L. Howard]

Matt,

I have the workbook.

Howard

 

[L. Howard]

Have a go at this version. Click the star in cell B5.

https://www.dropbox.com/s/la70n3ksiiulmbc/Q Book Transpose Format.xlsm?dl=0


Here is the code, transposes and formats.

Howard

Option Explicit

Sub Transpose_Color()
'/ by Claus @ MS PUBLIC

Dim varData As Variant, varHeader As Variant
Dim varOut() As Variant
Dim i As Long, j As Long, m As Long, n As Long
Dim LRow As Long, LCol As Long, myCnt As Long
Dim lngClr As Long

With Sheets("Tracker")
    LRow = .Cells(Rows.Count, 3).End(xlUp).Row
    LCol = 212
    For i = 7 To LCol Step 2
        myCnt = myCnt + Application.CountA(.Range(.Cells(9, i), .Cells(LRow, i)))
    Next
    
    varData = .Range(.Cells(9, 1), .Cells(LRow, 212))
    ReDim varOut(myCnt - 1, 2)
    For i = LBound(varData) To UBound(varData)
        m = 0
        varOut(n, m) = varData(i, 1)
        LCol = .Cells(i + 8, Columns.Count).End(xlToLeft).Column
        For j = 7 To LCol Step 2
            m = 0
            If Len(varData(i, j)) > 0 Then
                m = m + 1
                varOut(n, m) = .Cells(5, j)
                Select Case varData(i, j)
                    Case "Y"
                        lngClr = vbBlack
                    Case "R"
                        lngClr = vbBlue
                    Case "N"
                        lngClr = vbRed
                End Select
                varOut(n, m + 1) = lngClr
                n = n + 1
            End If
        Next
    Next
End With

With Sheets("Request")
    .Range("A61:Q1000").ClearContents
    .Range("A61:Q1000").Font.ColorIndex = xlAutomatic
    .Range("A61:Q1000").Borders(xlEdgeBottom).LineStyle = xlNone
    .Range("A61").Resize(n) = Application.Index(varOut, , 1)
    .Range("M61").Resize(n) = Application.Index(varOut, , 2)
    .Columns("M").AutoFit
    .Columns("M").HorizontalAlignment = xlCenter

    For i = 61 To 60 + n
        With .Cells(i, "M").Font
            .Color = varOut(i - 61, 2)
            .Bold = .Color <> vbBlack
        End With
    Next
    For i = 62 To 60 + n
        If Len(.Cells(i, "A")) > 0 Then
            With .Range(.Cells(i - 1, "A"), .Cells(i - 1, "Q")).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .Weight = xlThick
            End With
        End If
    Next
    With .Range(.Cells(60 + n, "A"), .Cells(60 + n, "Q")).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .Weight = xlThick
    End With
End With

End Sub