I'm trying to summarise data where each row of data is a particular test.
Each subject may have multiple tests and each student may have multiple subjects.
We don't know how many subjects per student or how many tests they will face.
Is there a way to summarise this data by student and as the secondary, by each subject (for each student)?
Example data:
Name Class Score
John English 77
Lisa English 49
Erik English 46
John Maths 23
Mark English 79
John Science 12
Jess English 69
Lisa Maths 47
Erik Maths 21
Jess Maths 12
Mark Maths 37
John English 82
Lisa English 61
....................................
end result ideally will look something like...
John English 80
Maths 23
Science 12
Lisa English 55
Maths 47
....................................
Is there a way of doing this in excel without knowing how many subjects for each student and number of tests, etc?