Excel/Teams problem, grades spreadsheet and how to reformat it to be more useful!

[nikaso]

Hi all,

 

We have Teams groups for each registration form and each form gets issued about a 100 assignments. These assignments are tied to one of three awards, complete a certain number of assignments for that award and you are awarded it. My problem is for reporting on award completion percentages and on whether individual students have done enough to gain an award.

 

The grades section in each Team allows you to download a spreadsheet of all the assignments to date that the students in that form have been issued along with whether they have completed them (by being given a 100 points for a completed assignment) The problem is each student has a row for every task so you have a spreadsheet with 3000 rows that isn't particularly easy to interpret!

 

This is some example data of what it currently looks like:

 

 

Ideally I would like a spreadsheet where every student has a single row and each assignment a single column, the intersecting cells would have the value 100 in them if a student had achieved that award. This would be much easier to interpret and produce percentage completion stats from. I can't think of an easy way to do this with a formula but can't help but feel it must be doable!

 

Here is the file itself if you want to have a play

ExcelProblem.xlsx

 

Any help or advice gratefully recieved!!

[Steve21]

Pivot table... done!

 

Edit - Helps if I attach copy!

 

Steve

[bobsmith]

This is the way.

[nikaso]

Pivot table... done!

 

Edit - Helps if I attach copy!

[ATTACH=CONFIG]71812[/ATTACH]

 

Steve

 

It now dawns on me I have explained the primary use case for Pivot Tables! Thank you so much!