GSKov

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
something like this
or I misunderstood...

screenshot-30.png
 
Upvote 0
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:
Upvote 0
sandy - yes, your second example is what I am attempting! What's the dependency to make that happen?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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

screenshot-32.png


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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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