• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Non-Nest Rows In Pivot Table

I have 3 fields (Skill1, Skill2 and LastName) in a pivot table, if I add the three fields as rows I get the following nested report. To add to the confusion Skill1 and Skill2 both have the same possible text contents; Excellent or Very Good or Good or Fair or Poor.​

Nested View

Skill1 Skill2 LastName
Excellent
Fair​
Good
Fair
Mcmahon​
Good
Larsen​
Poor
Goodman​
Very Good
Campbell​
Poor
Excellent
Ramos​
Good
Jensen
Richardson​
Poor
Crawford
Huber​
Very Good

I would like the fields in this order Skill2 directly below Skill1, not have Skill2 nested under Skill1 as in the above.

Desired non-nested view, is this possible?

Skill1 LastName
Excellent
McMahon​
Fair
Smith​
Good
Campbell
Goodman
Larsen​
Poor
Crawford
Huber
Jensen
Ramos
Richardson​
Very Good
Jones​
----
Skill2 LastName
Excellent
Ramos
Smith​

Fair
Bright
Henson
Johnson​
Good
Foster
Jensen
Larsen​
Poor
Crawford
Gallegos
Goodman​
Very Good
Campbell
Foreman​
 
Hi Al ,

Would it not be better if the data were laid out so that the names are the row labels , while the Skills are the column labels ? The grades in each skill can be the values ; this entire layout can be done through formulae.

Narayan
 
Hi Al ,

Would it not be better if the data were laid out so that the names are the row labels , while the Skills are the column labels ? The grades in each skill can be the values ; this entire layout can be done through formulae.

Narayan

What do you mean, when you say the "entire layout can be done through formulae." Can you show an example of what you mean.
Thanks
Al
 
Hi Al ,

Your table is already laid out in that manner , where you have the names down columns A and B , while the skills are in columns T through X.

What is the objective of creating a pivot table ?

You can always have a provision for sorting and filtering on this table , so that :

1. Given an employee name , you can view their skills and experience

2. Given a particular skill , you can sort the employees on their skill level

Narayan
 
Hi Al ,

You can set up a Custom List for sorting , as follows :

Excellent
Very Good
Good
Fair
Poor

Once you add this Custom List , you can sort your table on the skills in columns T through X using this custom list.

Narayan
 
I am trying to set a pivot table to generate these charts. Then be able to select a bar and see the Names of the people who make up the bar.

SkillCharts.png
 
Hi Al ,

I am sorry but I am still not able to understand how the charts help in any way ; given the data that you have , you can :

1. For any given skill , see the ranking of all the candidates

2. For any given grade , see who are the candidates with that grade in the various skills.

Both of the above can be done without charts. It is a different matter if you can make something like a heat map , where the X-axis is the skills , and the Y-axis is the grades ( Excellent through Poor ) , with the candidates names inside at the various points.

This is a time-consuming job.

Narayan
 
Back
Top