Can I do this in a Pivot Table

[markh9999]

I've tried using a pivot table for the following but I can't seem to get it to work. Basically my raw data has 500 names (I've just shown a sample below), a year group against each name and an activity against 3 different sessions.

I want to be able to summarise the data as shown below (so names become irrelevant).

Does anyone know a way?

RAW DATA

Name Year Session 1 Session 2 Session 3

Name 1 7 Activity 1 Activity 3 Activity 2
Name 2 7 Activity 1 Activity 2 Activity 3
Name 3 7 Activity 3 Activity 2 Activity 1
Name 4 8 Activity 2 Activity 1 Activity 3
Name 5 8 Activity 2 Activity 3 Activity 1
Name 6 8 Activity 2 Activity 3 Activity 1
Name 7 9 Activity 2 Activity 1 Activity 3
Name 8 9 Activity 1 Activity 2 Activity 3
Name 9 9 Activity 3 Activity 1 Activity 2

OUTPUT (Headings are Activity, year, session 1 total, session 2 total and session 3 total. For example, there are 2 year 7's doing Activity 1)


Activity 1 7 2 0 1
Activity 1 8 0 1 2
Activity 1 9 1 2 0
Activity 2 7 0 2 1
Activity 2 8 3 0 0
Activity 2 9 1 2 1
Activity 3 7 1 1 1
Activity 3 8 0 2 1
Activity 3 9 1 0 2