I have a simple spreadsheet with 4 columns: Student Name, Class, Semester & Major. An example of what I would like to see is all of the classes for the students who took Math1 in the Fall12 semester. If I Filter by Math1 and Fall12, the table will not show all of the classes those students are taking. Is there a simple answer to this? I figured out a way to do it with many filters in the basic data sheet. But, I'm thinking there is a clearner way with just the pivot capability. Many thanks!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Pivot Based on Another Pivot?
- Thread starter PeteGib
- Start date
Hi NickyvV. I hope this helps:
1. Starting with a list of students, their classes and the semester they will take each class…</SPAN>
[TABLE="width: 280"]
<TBODY>[TR]
[TD]Name</SPAN>
[/TD]
[TD]Class</SPAN>
[/TD]
[TD]Semester</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]Math 1</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]CHEM 1A</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]MATH 1</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]BIO 4</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jim</SPAN>
[/TD]
[TD]PHYS 2A</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jim</SPAN>
[/TD]
[TD]CHEM 1B</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jim</SPAN>
[/TD]
[TD]CHEM 12A</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jim</SPAN>
[/TD]
[TD]PHYS 2B</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jim</SPAN>
[/TD]
[TD]BIO 2</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]Math 1</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]MATH 55</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]MATH 37</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]CHEM 31</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]ENGR 11</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]MATH 20</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]Math 1</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]CSCI 7</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]MATH 1</SPAN>
[/TD]
[TD]Spr 14</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]ENGR 22</SPAN>
[/TD]
[TD]Spr 14</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]CSCI 14</SPAN>
[/TD]
[TD]Spr 14</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]MATH 20</SPAN>
[/TD]
[TD]Fall 14</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]ENGR 25</SPAN>
[/TD]
[TD]Fall 14</SPAN>
[/TD]
[/TR]
[TR]
[TD]Theresa</SPAN>
[/TD]
[TD]PHYS 4A</SPAN>
[/TD]
[TD]Fall 14</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
2. My question could be: Can I see all of the classes for the students taking Math 1 in Fall 12?</SPAN>
3. The answer would look like this…
[TABLE="width: 280"]
<TBODY>[TR]
[TD]Name</SPAN>
[/TD]
[TD]Class</SPAN>
[/TD]
[TD]Semester</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]Math 1</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]CHEM 1A</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]MATH 1</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane</SPAN>
[/TD]
[TD]BIO 4</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]Math 1</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]MATH 55</SPAN>
[/TD]
[TD]Fall 12</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]MATH 37</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]CHEM 31</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]ENGR 11</SPAN>
[/TD]
[TD]Spr 13</SPAN>
[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>
[/TD]
[TD]MATH 20</SPAN>
[/TD]
[TD]Fall 13</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
4. Can I used a pivot to get this kind of result?
Sorry, I can't delete the unrelated table below...
[TABLE="width: 210"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 108, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 108, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]
[/TD]
[/TR]
</TBODY>[/TABLE]
Last edited:
Upvote
0
Mavericks334
Active Member
- Joined
- Jan 26, 2011
- Messages
- 280
You can use Class and Semester as filters in your pivot to view which student attending these classes. Also, you can use slicers to get the same look if your using Excel 2010
Upvote
0
Thanks for trying. But, that would only leave me with two results. I was shooting for all the results in the example.
Upvote
0
Similar threads
- Question
