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

hope this helps, the second worksheet contains the layout i would like.

so I have had a good play with the power query tool and using the unpivot option. however it doesnt quite do what i would like it too as depicted in image 2. It has remove all null values and placed it in a vertical format however i need it to be horizontal.

anything else i could perhaps try?

Thank you very much Millz, This is exactly what i was needing. I'm very happy, keep up the great work!