Pivot Table for Student Data?

GSKov

New Member
Joined
Jun 20, 2017
Messages
8
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,320
or like this:

Student IDStudent NameTest1Test2Test3Test4Test5
1001​
Fred
65​
75​
80​
1002​
Wilma
95​
90​
100​
1003​
Barney
80​
85​
80​
1004​
Betty
100​
100​
100​
100​
1005​
Dino
50​
35​
55​
50​
1006​
Pebbles
90​
95​
95​
1007​
Bamm-Bamm
70​
70​
85​
1008​
Arnold P
80​
Total
390
380
565
590
0
 
Last edited:

GSKov

New Member
Joined
Jun 20, 2017
Messages
8
sandy - yes, your second example is what I am attempting! What's the dependency to make that happen?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,320
you are on 365 so you've PowerQuery aka Get&Transform, so use it :)

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Student ID"}, {{"Count", each _, type table}, {"Test1", each List.Sum([#"Test [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] "]), type number}, {"Test2", each List.Sum([#"Test [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] "]), type number}, {"Test3", each List.Sum([#"Test [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] "]), type number}, {"Test4", each List.Sum([#"Test [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL] "]), type number}, {"Test5", each List.Sum([#"Test [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5]#5[/URL] "]), type none}}),
    List = Table.AddColumn(Group, "Name", each List.Distinct(Table.Column([Count],"Student Name"))),
    First = Table.AddColumn(List, "Student Name", each List.First([Name])),
    Reorder = Table.ReorderColumns(First,{"Student ID", "Student Name", "Count", "Test1", "Test2", "Test3", "Test4", "Test5", "Name"}),
    RC = Table.RemoveColumns(Reorder,{"Count", "Name"})
in
    RC[/SIZE]
SourceResult
Student IDStudent NameTest #1Test #2Test #3Test #4Test #5Student IDStudent NameTest1Test2Test3Test4Test5
1001​
Fred
65​
1001​
Fred
65​
75​
80​
1002​
Wilma
95​
1002​
Wilma
95​
90​
100​
1003​
Barney
80​
1003​
Barney
80​
85​
80​
1004​
Betty
100​
1004​
Betty
100​
100​
100​
100​
1005​
Dino
50​
1005​
Dino
50​
35​
55​
50​
1003​
Barney
85​
1006​
Pebbles
90​
95​
95​
1004​
Betty
100​
1007​
Bamm-Bamm
70​
70​
85​
1005​
Dino
35​
1008​
Arnold P
80​
1006​
Pebbles
90​
Total
390
380
565
590
0
1007​
Bamm-Bamm
70​
1001​
Fred
75​
1002​
Wilma
90​
1003​
Barney
80​
1004​
Betty
100​
1005​
Dino
55​
1006​
Pebbles
95​
1007​
Bamm-Bamm
70​
1001​
Fred F
80​
1002​
Wilma F
100​
1004​
Betty R
100​
1005​
Dino F
50​
1006​
Pebbles F
95​
1007​
Bamm-Bamm R
85​
1008​
Arnold P
80​
 
Last edited:

GSKov

New Member
Joined
Jun 20, 2017
Messages
8
First of all - that's awesome! Unfortunately, our EDU version of O365 doesn't appear to include the Get&Transform module yet. It may roll out in a bi-yearly update, but not sure..

Does this mean that what I'm hoping to accomplish can't be done via a Pivot Table? It will instead require some level of coding?

Does anyone have any other maybe less elegant work-arounds for doing this?

Thx again,
Scott
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,320
Unfortunately, our EDU version of O365 doesn't appear to include the Get&Transform module yet.
Oh sh....! Pity :(

I'll try with PT but I can't promise something normal :devilish:
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,320
Student IDStudent NameTest #1Test #2Test #3Test #4Test #5NameStudent IDNameSum of Test #1Sum of Test #2Sum of Test #3Sum of Test #4Sum of Test #5
1001​
Fred
65​
Fred
1001
Fred
65​
75​
80​
1002​
Wilma
95​
Wilma
1002
Wilma
95​
90​
100​
1003​
Barney
80​
Barney
1003
Barney
80​
85​
80​
1004​
Betty
100​
Betty
1004
Betty
100​
100​
100​
100​
1005​
Dino
50​
Dino
1005
Dino
50​
35​
55​
50​
1003​
Barney
85​
Barney
1006
Pebbles
90​
95​
95​
1004​
Betty
100​
Betty
1007
Bamm-Bamm
70​
70​
85​
1005​
Dino
35​
Dino
1008
Arnold
80​
1006​
Pebbles
90​
PebblesGrand Total
390
380
565
590
1007​
Bamm-Bamm
70​
Bamm-Bamm
1001​
Fred
75​
Fred
1002​
Wilma
90​
Wilma
1003​
Barney
80​
Barney
1004​
Betty
100​
Betty
1005​
Dino
55​
Dino
1006​
Pebbles
95​
Pebbles
1007​
Bamm-Bamm
70​
Bamm-Bamm
1001​
Fred F
80​
Fred
1002​
Wilma F
100​
Wilma
1004​
Betty R
100​
Betty
1005​
Dino F
50​
Dino
1006​
Pebbles F
95​
Pebbles
1007​
Bamm-Bamm R
85​
Bamm-Bamm
1008​
Arnold P
80​
Arnold

add helper column eg. Name with formula:
Code:
=IFERROR(LEFT(B2,SEARCH(" ",B2)-1),B2)
then use this column in PivotTable



the assumption is that for each ID the names are unique
if not
for eg.
107 - Mark Axe
108 - Mark Buzzer
you'll see:
107 - Mark
108 - Mark
and you'll need to recognize by ID not by Name

it's very simple, maybe someone else will give you more flexible solution :)
 
Last edited:

GSKov

New Member
Joined
Jun 20, 2017
Messages
8
That looks really good.. Seems like that would allow for sorting by name as well.

I'm not getting the same result in my Example Sheet, tho. It looks like my PT Table Fields are configured the same as yours, but I'm getting expandable rows with both IDs and Student Names.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,755
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top