I need the quickest way to clean data...

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!

You have provided just two rows of data and nowhere do I see a mock-up of what you want it to look like once cleaned.

I am sorry, but I can't work out from your post or the workbook what the objective is. What will 'clean' look like?

OK - but I don't now what you want it to look like once 'cleaned'. Nobody wants to see sensitive data (you are told to desensitise what you do provide), but we do need 10-15 rows of sample data in both before and after format.

Is this what we are aiming for?

Excel 2016 (Windows) 32 bit

	A	B	C	D
5	Name	Email	Attribute	Value
6	John Smith	[email protected]	Site Location: / Sydney	1
7	John Smith	[email protected]	Department: / Maintenance	1
8	John Smith	[email protected]	Project Description:	Things
9	John Smith	[email protected]	Work Area Description:	sStorage Room
10	John Smith	[email protected]	Inspected by:	John Smith
11	John Smith	[email protected]	Position: / Training	1
12	Jane smith	[email protected]	Site Location: / Shop	1
13	Jane smith	[email protected]	Department: / Operations	1
14	Jane smith	[email protected]	Project Description:	Stuff
15	Jane smith	[email protected]	Work Area Description:	SHOP
16	Jane smith	[email protected]	Inspected by:	JANE SMITH
17	Jane smith	[email protected]	Position: / Maintenance Supervisor	1
18	Jane smith	[email protected]	Inspected by:2	John Doe
19	Jane smith	[email protected]	Position: / Maintenance Supervisor1	1
20	Jane smith	[email protected]	Inspected by:17	Jim
21	Jane smith	[email protected]	Position: / Maintenance Supervisor25	1

Sheet: Data

So just to be clear - any column with a 1 in it can be ignored?

EDIT: Forget that.

Maybe with this code to re-arange the data.

After that (maybe) a pivot table.

See the attached file.

Please Login or Register  to view this content.

UPDATE:

Still waiting for help with this project. I have tried the supplied code and the Query and I still am unable to get the data to display how I need it too.

I have attached an example workbook here with two tabs, one with 10 rows of data and one showing how I am trying to present the data.

PLEASE HELP!

Originally Posted by cwelsh

Do you think the query is the best way?

I couldn’t get it to do what you seem to want with the data - sorry.