Pivot Table Rookie

[lanashea]

Just explored Pivot Tables and I "get it" using numbers. I have a spreadsheet with students' 1st through 5th course choices. I was wondering whether I could use a pivot table that would summarize for each course, how many times it was selected 1st, 2nd, . . .5th. Possible?

See the attached SampleData file. I don't need to use the student's name. My specific data has hundreds of students and 13 courses from which each student selects his/her top 5 choices.

Thanks!

 

[Hui]

Lanashea

Firstly, Welcome to the Chandoo.org Forums

I would rearrange the data first


then add a PT


See attached file:

 

[lanashea]

Thanks, Hui!
I'm afraid that I didn't explain my needs very well.
For the end result, we would like to report totals of how many times each class was selected 1st, 2nd, 3rd, 4th, and 5th. We don't need to display student names.

 

[Hui]

Select a cell in the PT
In the PT Fields drag the Students out of the Rows dialog


See attached file:

 

[lanashea]

Hmmm. I'm still not communicating clearly. We would like to know how many times a class was selected as 1st choice, and another total telling us how many times that same class was selected as 2nd choice, and another total telling us how many times that class was selected as 3rd choice, etc., rather than a total number of times the class was selected 1st or 2nd or 3rd or 4th or 5th.

So, the end result would be each of the 13 courses as a row in column 1 and then 5 rows of totals labeled 1st choice, 2nd choice . . . 5th choice with totals in each cell.

 

[Chihiro]

Using Hui's data structure. Move "Subject" to Rows, "Choice" to Rows under "Subject", "Student" to Values (set it to count).

 

[lanashea]

Yes! So, secondary question . . . I've got a file of hundreds of student requests and each student is listed on 1 row with their 5 course choices as columns.
Is there a macro or quick way to change that format to 5 rows for each student, each row indicating 1 of his/her course choices?

 

[candybg]

There is a youtube video showing just how to do this - titled HOW TO FLATTEN A CROSSTAB TABLE:

I thought it was a very cool solution!

Hope this helps!

 

[lanashea]

Awesome resource! I'm almost there. I would desire the columns to be in order of FirstChoice, SecondChoice, ThirdChoice, FourthChoice, FifthChoice, but the pivot table is putting the column headers in alphabetical order: FifthChoice, FirstChoice, FourthChoice, SecondChoice, ThirdChoice. I'm sure this is an easy fix?

 

[Chihiro]

Take your mouse cursor to the right edge of column header you want to move. When the cursor turns into solid black cross, click and drag.

More details on PivotTable sorting in the link below.
http://www.contextures.com/excel-pivot-table-sorting.html

 

[lanashea]

From Zero to Hero in less than 24 hours! Thank you so much for everyone's assistance!!