Pivot tables with multiple headings

[CJL]

Hi,
I am trying to create a report in the following format (see attached file - copy of competency reporting.xlsx) using the data in this format ( see attached file - copy of dummy data.xlsx).
I'm having trouble getting a pivot table to generate this report. Please help.

Thanks,

 

[Faseeh]

Hi CJL,

Before you start working over this sheet, make sure that you can layout pivot table in the shape of your required report. Are you sure you can have a pivot table like your desired reports?

 

[CJL]

Hi Faseeh,
Thanks for getting back. I was unable to layout the pivot table in the shape of the required report. Do you have any suggestions ?
Thanks,
CJL

 

[Luke M]

Like Faseeh, I'm not sure we can get the exact layout on Report by Competancy. Having the columns show % of row is preventing me from doing a grand total showing average. But anyway, I got pretty close. First though, had to re-organize the data into a better table form for the PT. Did that with this macro

Sub Reorg()
Dim bUnit As String
Dim gLoc As String
Dim HR As String
Dim xCat As String
Dim c As Range
Dim i As Long
Dim xScore As Integer
Dim xName As String
Dim subComp As String
 
i = 2
Application.ScreenUpdating = False
Worksheets("Numeric").Select
 
For Each c In Range("E4:BB51")
    With c
        xName = Cells(.Row, "A")
        bUnit = Cells(.Row, "B")
        gLoc = Cells(.Row, "C")
        HR = Cells(.Row, "D")
        xCat = Cells(3, .Column)
        xScore = .Value
        subComp = Cells(1, .Column)
    End With
    
    With Worksheets("DataChanged")
        .Cells(i, 1) = xName
        .Cells(i, 2) = bUnit
        .Cells(i, 3) = gLoc
        .Cells(i, 4) = HR
        .Cells(i, 5) = xCat
        .Cells(i, 6) = xScore
        .Cells(i, 7) = subComp
    End With
    i = i + 1
Next c
Application.ScreenUpdating = True
 
 
End Sub

Then, starting building the PT. Hopefully this is close enough to what you want.

 

[Faseeh]

Hi CJL,

Try out Luke's code hopefully it will work.

 

[CJL]

Hi Luke,
Thanks very much for your help. I did use a long drawn method to do the calculations and I matched them with yours and they are very accurate. The calculations of the main competency i.e. the bolded numbers don't match. What formula did you use for them ? Also any thoughts on how we might get an average for the competency rating ?

Please could you explain how you managed to get the layout so close to the actual. What exactly did you do to the pivot to get it formatted this way. I'm not a highly advanced user so please bear with me.

Thanks again for your assistance.
CJL

Like Faseeh, I'm not sure we can get the exact layout on Report by Competancy. Having the columns show % of row is preventing me from doing a grand total showing average. But anyway, I got pretty close. First though, had to re-organize the data into a better table form for the PT. Did that with this macro

Sub Reorg()
Dim bUnit As String
Dim gLoc As String
Dim HR As String
Dim xCat As String
Dim c As Range
Dim i As Long
Dim xScore As Integer
Dim xName As String
Dim subComp As String
 
i = 2
Application.ScreenUpdating = False
Worksheets("Numeric").Select
 
For Each c In Range("E4:BB51")
    With c
        xName = Cells(.Row, "A")
        bUnit = Cells(.Row, "B")
        gLoc = Cells(.Row, "C")
        HR = Cells(.Row, "D")
        xCat = Cells(3, .Column)
        xScore = .Value
        subComp = Cells(1, .Column)
    End With
   
    With Worksheets("DataChanged")
        .Cells(i, 1) = xName
        .Cells(i, 2) = bUnit
        .Cells(i, 3) = gLoc
        .Cells(i, 4) = HR
        .Cells(i, 5) = xCat
        .Cells(i, 6) = xScore
        .Cells(i, 7) = subComp
    End With
    i = i + 1
Next c
Application.ScreenUpdating = True
 
 
End Sub

Then, starting building the PT. Hopefully this is close enough to what you want.

 

[Luke M]

Hi CJL,

I'll try to explain the PT structure. The By Name Pivot is the easier one. Loads Name, Business Unit, and Geographic Location in Row columns (in that order), and LowComp (single letter), SubComp, and Category in Column headers (apologies if I'm getting names wrong). Then Score goes into the data area.
To get the "flattened" look rather than the new outline format of 2007, I went to the PivotTable Tools DEsign Ribbon, Layout Group, Report Layout, Show in Tabular Form.
I then removed all Subtotals and Grand Totals (using the Design Ribbon again), and then right-clicked on each of the Column headers to add a subtotal. Since each row only represents a single record, using the sum is no problem (as the sum of a single number IS that number. )

The competency one has Business Unit, HR, and Geographic location thrown up into the Report filter. Rows are LowComp, SubComp, and Category. Scores goes into the Value area, AND into the column header (note that it's in 2 places). Right click on the Data label, Summarize Data by, More options...
I chose "count" on the first tab, since we want to know how many responses of each number there are, and on the 2nd tab (show value as), I chose the % of row.
I then unchecked the "0" responses from the column labels filter. Format numbers as percentage, 0 decimal places.

I think that does it for explaining how it's built.

Now, to really get that AVERAGE in there...
I borrow a trick from Debra's site (http://www.contextures.com/xlPivot13.html#TotalMulti) to create additional subtotals. In short, you'll see that there is now an extra field in the Column Labels and Values area. I've got columns now for both the average and % diff, but I've hidden the columns that I don't want to see.

 

[CJL]

Hi CJL,

I'll try to explain the PT structure. The By Name Pivot is the easier one. Loads Name, Business Unit, and Geographic Location in Row columns (in that order), and LowComp (single letter), SubComp, and Category in Column headers (apologies if I'm getting names wrong). Then Score goes into the data area.
To get the "flattened" look rather than the new outline format of 2007, I went to the PivotTable Tools DEsign Ribbon, Layout Group, Report Layout, Show in Tabular Form.
I then removed all Subtotals and Grand Totals (using the Design Ribbon again), and then right-clicked on each of the Column headers to add a subtotal. Since each row only represents a single record, using the sum is no problem (as the sum of a single number IS that number. )

The competency one has Business Unit, HR, and Geographic location thrown up into the Report filter. Rows are LowComp, SubComp, and Category. Scores goes into the Value area, AND into the column header (note that it's in 2 places). Right click on the Data label, Summarize Data by, More options...
I chose "count" on the first tab, since we want to know how many responses of each number there are, and on the 2nd tab (show value as), I chose the % of row.
I then unchecked the "0" responses from the column labels filter. Format numbers as percentage, 0 decimal places.

I think that does it for explaining how it's built.

Now, to really get that AVERAGE in there...
I borrow a trick from Debra's site (http://www.contextures.com/xlPivot13.html#TotalMulti) to create additional subtotals. In short, you'll see that there is now an extra field in the Column Labels and Values area. I've got columns now for both the average and % diff, but I've hidden the columns that I don't want to see.

Hi Luke,
This is perfect. Thanks a lot. Just one more thing. I noticed the Row Labels are arranged alphabetically for each competency i.e. A, B etc. and therefore the sub-competencies are not in order i.e. A1, A2, B1,B2 etc. Can this be fixed ????
Thanks,
Clinton

 

[Luke M]

You can manually drag the rows to re-arrange them in the order you want. The problem is that the Long names are in alpha-order, and then the PT is sorting (incorrectly, IMO) the B10 before B7, because it looks at the 2nd character only when doing the comparison between the two.

 

[CJL]

You can manually drag the rows to re-arrange them in the order you want. The problem is that the Long names are in alpha-order, and then the PT is sorting (incorrectly, IMO) the B10 before B7, because it looks at the 2nd character only when doing the comparison between the two.

Thanks a lot Luke. Much appreciated

 

[Luke M]

No worries CJL, it was a fun challenge.