HELP! Data clean up...

I have a report that I pull from a program for work... I need to find the best way to clean up this data and present it but I cannot seem to figure it out... the way the data is created is that someone fills out an online form, then it will add a new line and put in a 1 for each choice made, unless it's a text field. I cannot change this aspect of the way the data is collected or the way the report is presented (the frist tab of my attached example workbook) when I pull it. Therefore, I am trying to manipulate the data into a nicer looking, easier to read table (example in sheet 2 of my attachment).

I have tried using the delete coloumns option (delete all coloumns with no data) this doesn't work as there seem to be hidden characters that are causing the coloumns to read as not blank.
I have tried using pivot tables but with all the coloumns its very hard to pull what I need
I have tried manual...sooo time consuming.

I am looking for someone who wants to take a look and help with this project... I am familiar with VBA and am hoping there is a way to just click a button and have it sort out where the data is. I have a code that allows me to do this but it pulls all the coloumn headers and the respective responses...but again I have no way to manipulate it.

I have attached an example work book. This shows how the data is collected/presented in excel and in this example:
John Smith selected Sydney in the site location drop down on the fillable form so a 1 is added there, Maintenance for Department (again another 1), then filled in Things for project description and typed in Storage Room with and error under work area description, then filled in his name under inspected by along with selecting Training for his position.
Jane Smith selected Shop in the site location drop down on the fillable form so a 1 is added there, Operations for Department (again another 1), then filled in stuff for project description and typed in shop Room under work area description, then filled in her name under inspected by along with selecting maintenance supervisor for her position. She then added a couple other names for co-inspectors... Which the form allows etc...


The coloumn count goes to XR (I removed many of them to keep it clean for this example) and so I would love a way to just pull out the relative info instead of just manually sorting!

I also added in a VBA code that if you run the macro will 'rearrange' the data like a query but.... then what?

If you are up for a challenge I would REALLY appreciate any help!

Thank you,
Chantel