transformation of data?

I got this data (Progress report raw data) from a school software programme with teacher scores for different student behaviours, eg puntuality, organisation, etc, each in a separate worksheet. Stupidly, the software dumped this with all 210 student names in Column A with the particular behaviour for every subject on the curriculum on Row 1.To make matters worse, for each subject, there are 2 teachers, listed eg, punctuality A&D 3D (Art and Design 3D) and punctuality A&D 3D Tb (ie Teacher b).

The format I want the data to be in is in the Report data desired data sheet, where the behaviours are in columns D to H and each student is listed in Column A with Column B denoting the subject (say, 1=A&D 3D, 2=Maths, etc) and the score from Teacher a and Teacher b. This way, I can sort the data SO much more easily in terms of who is has the worst punctuality overall, etc.

I'm an experienced Excel user and I'm having several different attempts at transposing this data but I'd welcome anyone else's input on this. I just need a few pointers.

Thanks in adavance

Schallpattern