Changing Rows to Columns

irst of all, I know there is a way to do so using paste special>transpose. But it doesn't work for my case.

Ok here's part of my worksheet :




There are around 100+ entries of the same format.

What I want to do is change all of the rows into columns like this :




Where only the Name/Department/Faculty etc only appear once and the respective values go into the respective collumns.

Is there a VBA code to do it or can it be done without? Appreciate any help given!