Unmerge Columns to structure data

[dj2dirk212]

Hi All,

I am using the US census data and am trying to transpose the data however the challenge I am having is that the columns have merged cells. I dont think you can use multi-row since this the data is in the columns. Is there a way to unmerge the column header then repeat the data for each column?

Ideally I would like to have the final data look something like the picture attached

[SPetrie]

Are you needing all the columns, or just the household estimate column? I only see household estimate in the example PNG

[dj2dirk212]

For this specific dataset I only need household however I was hopping to leverage a workflow for other datasets that may have two or more subcolumns. 

[SPetrie · Accepted solution]

Im not sure how applicable this will be for your uses but I think it at least gives an idea of one possible way to go about it.

In this case, I imported the file indicating that the first row has data. Then I split out the header row that has the zips and then the row that has the sub type.

I transposed the zips and cleaned them and used a multi-row to copy them down to the merged cells that show us a blank in Alteryx.

I filtered out the sub types to be only the one (or ones) I care about. I also filtered the main data to be only the income group rows I cared about.

After that, I used find/replace and the generic column headers to append the names back to the transposed main data.

Hopefully this makes sense and helps.

[dj2dirk212]

This is fantastic. For the intended purposes this seems to have worked Ill have to see how I can scale this in other areas.