Condense data

Hi All,

I would like some suggestions or a simpler way of how I can condense this data I have into an easier to read spreadsheet.

Image 1 (attached) for example has the original data. It contains the students name, class and a colour of Green, amber or red for each subject the student does. As i took a screen grab of the first half of the spreadsheet the empty columns would be populated further below for a different group of students.
If a student was to have 7 subjects you can understand that this data would be well spread out and quite hard to read at first glance.

what i would like to do is to condense this into Image 2 example. While i can do this manually it is still a very time consuming process. And if there is an automated way it would be much easier. if this could be done on a new worksheet even better. My knowledge of excel is quite green so any help appreciated.

thank you