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 sheet 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 its a text field. I cannot change this aspect of the way the data is collected.
I have tried using the delete coloumns option (delete all coloumns with no data)
I have tried using pivot tables
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 attached an example work book. This shows how the data is collected 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.
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!
Bookmarks