Reporting tabular Data with Summary of Common Column Data above details.

[dcbarry]

Its been a long time since I've worked deeply in Excel. (Im using Excel for Mac 2011, FYI).

In this case I'm dealing with exported data in a table. My example is a gross simplification, to get to the basic question.

Assuming I have data like below, that is sorted and repeats, I'd like to be able to be able to print a leading row for the common (repeating) data, and then detail lines. Preferably, I'd leave the base data alone, and build the different versions of reports (with different columns) on distinct worksheets.

(again, keeping it simple in my trivial example, but you can imagine an additional column named "school", which would lead to 2 level tiering. )

Data:

[TABLE="width: 360"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Teacher[/TD]
[TD]Student[/TD]
[TD]Absent[/TD]
[TD]GRADE[/TD]
[/TR]
[TR]
[TD]Mr. Jones[/TD]
[TD]David[/TD]
[TD]0[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Mr. Jones[/TD]
[TD]Edward[/TD]
[TD]0[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Mr. Jones[/TD]
[TD]Frank[/TD]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Ms. Smyth[/TD]
[TD]Alice[/TD]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Ms. Smyth[/TD]
[TD]Bob[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Ms. Smyth[/TD]
[TD]Charlie[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Dr. Bones[/TD]
[TD]Indy[/TD]
[TD]3[/TD]
[TD]D+[/TD]
[/TR]
[TR]
[TD]Dr. Bones[/TD]
[TD]Julie[/TD]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]


Print Report as:

[TABLE="width: 253"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][FONT=&quot]Mr. Jones[/FONT][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]0[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]0[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][FONT=&quot]Ms. Smyth[/FONT][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alice[/TD]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dr. Bones[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Indy[/TD]
[TD]3[/TD]
[TD]D+[/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Of course, I went straight to pivot tables, until i remembered they are not actually suited for this, as the actual column data is what i want, not counts or computations such as averages on that data. Groups also do not seem to be the answer.


If there is something straightforward I have overlooked, I would appreciate the advice on how to accomplish this.

 

[Smitty]

Welcome to the Board!

You can do what you want with a PivotTable, just put all of your fields in the Rows section, not the Values section. Add a Slicer for teacher, and you should be good to go. Another trick is to put Teacher in the PivotTable Filter Field, then go to PivotTable Tools > Analyze > Options > Show Report Filter Pages, and Excel will create new worksheets for each teacher in the list.

Unfortunately, I don't have a Mac in front of me at the moment to test.

HTH

 

[dcbarry]

Am I not following you? If I put student, Abscense, and grade in rows, while it leaves the data in altered, it comes out as a triple nested data, not as a row of data per student. Is there some option I am not seeing to flatten that?

 

[Smitty]

Go to PivotTable Tools > Design > Report Layout > Show in Tabular form