Pivot Table for Student Data?

[GSKov]

Hi there. I'm trying to find a way to do something for teachers in my school district. They would like to see testing data from several different platforms aggregated by student. The issue I'm facing is getting the Pivot Table to aggregate the scores by Student ID but show only one instance of the Student Name as the row label.

Since the source data on the Raw Data tab will be coming from different platforms, the Student ID will always be correct but the name may be listed differently (Last, First or Last, First Initial, etc.). However, teachers will want to see the aggregated data by Student Name.

I've put together an example Excel Sheet and shared it via Office 365:

https://meramecvalley-my.sharepoint...dImLJfbxTDgzcBsVH38NJXZ1Y-jF_7DEq04A?e=OyDBE0

Maybe I'm on the wrong track and there's a different method to achieve this. I would appreciate any guidance on how to structure things. Thanks for reading!

Cheers,
Scott

 

[GSKov]

Hmm.. I see in your PT that both the ID and Name columns are separate, and each has a drop-down button.

In my example, rather than having two separate columns, I keep getting one column header called "Row Labels" and the Name is nested beneath the ID. I've tried it several times, and my Google Fu isn't leading me to an answer...

You can click HERE for the original Sheet, if you still have time to donate to my issue!

Thx,
Scott

 

[sandy666]

open file in Excel (not Excel Online!)
Insert PivotTable
add fields to appropriate areas
select any cell in pivot table
in PivotTable Tools - Analyze find Show and unclick +/- buttons
in PivotTable Tools - Design go to the left Report Layout - Tabular Form
in PivotTable Tools - Design go to the left Subtotals - Don't show Subtotals
don't use Student Name but column NAME only (you didn't see it was unchecked on the picture???)

 

[sandy666]

Pivot Structure again

read and learn: Pivot Table

 

[GSKov]

Hi again. I have been working in O365 only.. I guess I can configure everything as needed in the desktop version of Excel, and then upload to OneDrive for teachers to be able to collaborate online.

I've not had the Student Name field checked anywhere, though - just Student ID and Name in the Rows Labels field.

However, I think your helper column idea will do the trick, and I will simply use only Name as the Rows Label. That appears to aggregate the data correctly, and it allows sorting alphabetically by name as well.

 

[sandy666]

sure, but read end of the post#8 about unique names

hope it was helpful