Long data copied to wide data

I assume you open the XLSX file in Numbers and that you do not have Excel on your Mac. I haven't used Numbers for years so I don't really know it all that well. But if you can get the data in the form demonstrated in the picture below "Table1" with candidate names on all rows and remove rows with only the names, you can create a pivot table from it (Rows: candidate, Columns: question, Values: sum of Raw). and make sure the raw and max values are numbers not texts.

This will do the transposition for you. Now I think you can't do much more with that pivot table in Numbers, other than to copy it and paste it as a regular table. Then you can insert your sub-total columns, percentages and the extra header row for the Max scores for each question.

If you have Excel on your Mac, I would skip Numbers altogether. But that's just my personal opinion.