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]

Hi Howard

There is no colour coding needed on Sheet 1. The colour coding only appears on sheet two - but there would be nothing wrong with colour coding on sheet one if that made it easier - in short........

It can appear on sheet 1.
It must appear on sheet 2.

We have always in the past done the sheet 2 form manually, and added the colour where needed line by line as needed. It is how the budget controllers react to each request. Blue - I am asking for an addition to the structure, therefore going to cost money, red means removal from the structure therefore going to save money!

Does that make sense?

Matt

 

[L. Howard]

If the color coding is a result of "your thought process" while parsing the data and considering information known only to you, and not available to the person writing the code, then that is something that can NOT be coded.

Which would be construed as having no structure for the coder to rely on.

Howard

 

[mattyn]

The colour coding is due to the result of the qualification against the position on work sheet 1 - N = Red, R = Blue.

Does that mean that coding is not possible for this particular work book?

 

[L. Howard]

The colour coding is due to the result of the qualification against the position on work sheet 1 - N = Red, R = Blue.

Does that mean that coding is not possible for this particular work book?

And Y is plain text, no color?

Howard

 

[mattyn]

That is correct.

 

[L. Howard]

And to add, so there are formatting errors in your posted example:

Pos 2 Q2

Pos 3 Q2

Howard

 

[mattyn]

You are right .... They are round the wrong way!
An easy mistake to make especially with a huge grid. You can see why I need to automate it!

Matt

 

[L. Howard]

Okay, we'll go with that.

May have more questions, but good for now.

Howard

 

[mattyn]

Excellent thank you.

 

[L. Howard]

Hi Matt,

Give these a go. Copy to a standard module.

Assumes the two sheets are named Sheet1 and Sheet4.
Edit sheet names in code as necessary or test on a new workbook with a sheet1 and a sheet4, with your huge grid copied to sheet1.
For testing, I advise the latter

Your huge grid will be on sheet1 and Transpose will render it to sheet4 in the configuration per your example.

If it works well then run Format for the color-coding of the Q's on sheet4.

If that works well then you can uncomment the call for macro Format at the bottom of Transpose, and the two will run together by running Transpose.

Howard

Option Explicit

Sub Transpose()
'/ by Claus ay 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

varHeader = Array("Position", "Name", "Qualification")
With Sheets("Sheet1")
    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    LCol = .Cells(2, Columns.Count).End(xlToLeft).Column
    For i = 4 To LCol Step 2
        myCnt = myCnt + Application.CountA(.Range(.Cells(3, i), .Cells(LRow, i)))
    Next
    
    varData = .Range(.Cells(3, 1), .Cells(LRow, LCol))
    ReDim varOut(myCnt - 1, 2)
    For i = LBound(varData) To UBound(varData)
    m = 0
        varOut(n, m) = varData(i, 1)
        m = m + 1
        varOut(n, m) = varData(i, 3)
        m = m + 1
        For j = 4 To LCol Step 2
            If Len(varData(i, j)) > 0 Then
                varOut(n, m) = .Cells(1, j)
                n = n + 1
            End If
        Next
    Next
End With
Sheets("Sheet4").Range("A1").Resize(, 3) = varHeader
Sheets("Sheet4").Range("A2").Resize(myCnt, 3) = varOut

Sheets("Sheet4").Range("A1:C1").Font.Bold = True
Sheets("Sheet4").Range("A1:C1").HorizontalAlignment = xlCenter

'Format
End Sub

Sub Format()
'/ by Claus ay MS Public

Dim varData As Variant
Dim myStr As String
Dim LRow As Long, i As Long
Dim myRng As Range, myN As Range, myQ As Range
Dim myClr As Long
Dim blnBold As Boolean

With Sheets("Sheet4")
    LRow = .Cells(Rows.Count, 3).End(xlUp).Row
    varData = .Range("B1:C" & LRow)
End With

With Sheets("Sheet1")
    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set myRng = .Range("A1:I" & LRow)
    Set myN = .Range("C1:C" & LRow)
    Set myQ = .Range("A1:I1")
End With

For i = 2 To UBound(varData)
    If Len(varData(i, 1)) > 0 Then myStr = varData(i, 1)
    With Application
        Select Case .Index(myRng, .Match(myStr, myN, 0), .Match(varData(i, 2), myQ, 0))
            Case "R"
                myClr = vbBlue
                blnBold = True
            Case "N"
                myClr = vbRed
                blnBold = True
            Case Else
                myClr = vbBlack
                blnBold = False
        End Select
    End With
    With Sheets("Sheet4").Cells(i, 3).Font
        .Color = myClr
        .Bold = blnBold
    End With
Next
    
End Sub