I regularly receive sheets that contain merged cells used to group data with rows of data laid out properly in columns below. I want to be able to convert this all to columnar data. Can I use Unpivot to do this once I have unmerged the cells? I have attempted to use fill down to get some of the data cleaned up as well as splitting the report into multiple tables, thinking then I could clean it and then merge it. Am I on the right track? Attached workbook shows the original report, Headers = data I cleaned manually to show which info must be converted to columns, Data = example of rows of data.
My goal is to have all the data in columns for use in queries and pivot tables - but the clean up is daunting. Thank you for any suggestions.
Cheryl
Hi Cheryl,
I don't blame you for feeling daunted. That report sure is ugly.
In the attached file I've cleaned and unpivoted the data and you'll see it on the Query Output sheet. You might be able to build the query more efficiently than I have as I'm not familiar with the data so was removing things as I went, but some steps could be consolidated or done in a more efficient order.
Mynda
Thank you - I may be able to use that query for part 1 of my data cleanup. Then rejoin it to the remaining data being student entries shown below these summary headings.
