Restructuring and Condensing Data Set

[shelbybradley]

Hello, 

I am having trouble restructuring my data set to only include the critical columns I need. The data currently looks like this, with the critical info underneath a bunch of extra data. 

Output:

I would like the data to have the column structure be condensed to:

- Product

- Week (with all the dates in one column)

- Demand

- Stock

- Supply

- GAP

Any help is much appreciated. 

Thanks

[RolandSchubert]

Hi @shelbybradley ,

I assume, the rows without product/region are the totals for the rows above (e.g. 20-23 are total of 3-19)?

If that is true, you could remove the second header line, fill the empty product field for the total rows using a Multi-Row Formula tool, transpose the weeks, calculate the totals and use a Cross Tab tool to  move the measure to columns.

What do you think?

Best,

Roland

[Blake · Accepted solution]

Is this what you're looking for? I left 'Inputs' as a key field but that can easily be removed. See attached for the .yxzp