Pivot on a pivot

[JonBristol76]

Hi, I've been creating a skills matrix with row titles of skills and column titles of job roles. Then the data in the table allocates required skills levels of those skills against roles. This gives me a nice matrix of all the roles, and all the skills. For example:

Skills matrix

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>*</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td>*</td><td>*</td><td colspan="3" style="text-align:center; ">Role</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td>*</td><td>*</td><td style="font-weight:bold; ">Engineer level</td><td style="font-weight:bold; ">Supervisor</td><td style="font-weight:bold; ">Manager</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td rowspan="4" style="text-align:center; ">Skill</td><td style="font-weight:bold; ">Shape</td><td>Skilled</td><td>Expert</td><td>*</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style="font-weight:bold; ">Bake</td><td>Skilled</td><td>*</td><td>*</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style="font-weight:bold; ">Eat</td><td>Skilled</td><td>*</td><td>Skilled</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style="font-weight:bold; ">Manage</td><td>*</td><td>Skilled</td><td>Expert</td></tr></tbody></table>

The problem is, with 10 roles, and about 30 different skills (with text descriptions, not just the one word in the example) it's got quite clunky. What I want to do in another worksheet is just take the role and show the skills for that role... without gaps. For example the 'Supervisor' worksheet would give:

Supervisor Summary Sheet:

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>*</td><td>C</td><td>D</td></tr><tr style="height:23px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td>Role</td><td style="background-color:#ccffcc; font-weight:bold; ">Supervisor</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td>*</td><td>*</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td style="font-weight:bold; ">Skill</td><td style="font-weight:bold; ">Skill level</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td>Shape</td><td>Expert</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">32</td><td>Manage</td><td>Skilled</td></tr></tbody></table>

It's like I'm trying to do some INDEXs & Matches, or lookups on another sheet, but if I do that it creates the spaces too. It feels like a pivot table to me, but for the life of me I can't get it to display correctly.

What would be the cherry on the cake is if I could have a drop down of roles (like in a pivot) and choose the role, then the summary pops down below it.

Anybody have any thoughts?

 

[Fazza]

hi, Jon

First thought is to change the data structure.

Currently as a matrix it is already in a report structure. That is not the way to store data. It is the way to report data.

Instead store the data with three fields: Skill, Role, Skill level.

Then the reports should be simple. No formulas required.

What do you think?

regards, Fazza

 

[JonBristol76]

Thanks Fazza,

I've slightly simplified my explanation but the 'Skills Matrix' I've described is actually a report on another table of data. This is my problem, i've created a report on a report... now I need a way of losing the row gaps in a third step.

Perhaps if I explain a different way:
- I make a table of data on worksheet 1 with columns A, B, C, D and multiple rows
- Each row has a value in column A but some of the other columns don't values in each row.

- I would like to create a table in worksheet 2 which only displays the values of the rows which have a value within both column A and... say, column C. i want it to ignore all rows which don't have values in the column C


Thinking about it, that's the simplest way to explain the challenge...

Any thoughts?

Adrian