Cleaning/Reformatting Unorganized Data in Excel

Question

Hi! Hope you are doing well! I have some data in the attached Excel that I want to clean and reformat. Currently the columns and rows are confusing and I want to have just row 22 as the headers and any row that says "PERIOD" or "Subtotal for Product Type" to also be a header/column. I don't need anything from rows 21 and above, as well as anything that mimicks that below. Thank you in advance!

Data Example.xlsx

mceleavey · Answer

Hi @etchiou ,

When you input your Excel sheet you have an option to start importing on a given row:

If you configure your tool as above, you can see the "Start Data Import on Line" section, this will skip the rows above your header row.

As for the rest, you will need a combination of the Preparation Tools and the Transform Tools.

You can find more information about these tools below:

Pivoting your data: INTERACTIVE LESSON

The Transpose tool.

The Crosstab tool.

I hope this helps,

M.