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

dcbarry

New Member
Joined
Sep 20, 2018
Messages
2
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:

TeacherStudentAbsentGRADE
Mr. JonesDavid0A
Mr. JonesEdward0A
Mr. JonesFrank1B
Ms. SmythAlice3C
Ms. SmythBob2B
Ms. SmythCharlie3A
Dr. BonesIndy3D+
Dr. BonesJulie3B

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


Print Report as:



[FONT=&quot]Mr. Jones[/FONT]
David0A
Edward0A
Frank1B
[FONT=&quot]Ms. Smyth[/FONT]
Alice3C
Bob2B
Charlie3A
Dr. Bones
Indy3D+
Julie3B

<colgroup><col><col><col></colgroup><tbody>
</tbody>
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top