Data Transformation Help

Question

I have attached an example set of data that is the input and how I would like the output to be. The problem I have is that the names and products are merged, the names are column headers so trying to figure out how to move them into their own field and want it all to be dynamic as there could be more names and products added in the future. Any help or suggestions appreciated.

Example Data.xlsx

phottovy · Accepted Answer

Hi @Travis_Ratliff , this isn't the cleanest solution but it is possible with a little creativity and by using some cross-tab and transpose tools to transform the data. In this example, I split the logic for the header columns from the numeric columns and then join everything back together. The only difference between my final output and your is I keep the dates and mine sorts differently than yours:

TheMattLeonard · Accepted Answer

I deal with this kind of thing a lot. The key is to Transpose your whole data set so that the headers that are "blank" (because in excel they are covered by a merged cell, in your case the franchise name) can easily be filled back in via the Multi-Row Formula Tool. I don't have the time to just do the workflow for you but I can give you some rough instructions and you should be able to work it out.

* In your Input data tool, make sure "First Row Contains Data" is checked so you get F1, F2, etc as your headers
* Add RecordID as a tool. Not strictly necessary but I like having it there
* Add a Transpose Tool. Youre gonna want to group on F1 (date) and Record ID but nothing else. All the other columns should be Data columns, including “Unknown” so it’s dynamic
* Split your data into Data and Headers with a Filter. In your case, Row 3 and below to False, Everything else to true.
* Off your false branch (headers), use a multirow formula tool to “drag down” the values
* Continuing off this branch, use a crosstab (group on - Name, Column Headers - F1, Values – Value)
* Go back to your filter and join the T Output to the new output from the Crosstab. Join on Name (column)

Hope this helps!