Splitting Data by Dynamic Column Contains Reference

[mmvcans]

Hi all!

I have data as follows:

period	x	x	x	y	y	y	z	z	z
	a	b	c	a	b	c	a	b	c
1	[data]	[data]	[data]	[data]	[data]	[data]	[data]	[data]	[data]
2	[data]	[data]	[data]	[data]	[data]	[data]	[data]	[data]	[data]
3	[data]	[data]	[data]	[data]	[data]	[data]	[data]	[data]	[data]

I need a method for bucketing the fields based on the first column header (x, y, and z) and then sub-header (a, b, and c). 

The workflow needs to be dynamic by referencing the header and sub-header to work across sheets with different number of columns. The end goal is to put this into an iterative macro to get data into standard format, by period, prior to analysis where the data would look something like:

period	header	sub-header	data
1	x	a	[data]
1	x	b	[data]
1	x	c	[data]
1	y	a	[data]
1	y	b	[data]
1	y	c	[data]
1	z	a	[data]
1	z	b	[data]
1	z	c	[data]

I'm struggling to manipulate the transpose tool to create a dynamic reference and wondering if there are better solutions out there.

Thanks!

Mike

[fmvizcaino · Accepted solution]

Hi @mmvcans ,

Since Alteryx has not the ability to manipulate 2 row header, we need to split it into 2 separate cases to accomplish what you are looking for. 

If you are using an input data tool to load your data, select the option to turn 1 rows into data instead of headers.

Best,

Fernando Vizcaino