Transform and Reporting Tools - Grouping columns

[persevere]

Hi,

Can anyone give me some advice on how I can achieve the following with the Transform and Reporting tools.

Basically I have table A, the output of an Alteryx workflow: it has five columns: Item No, Year, Metric 1, Metric 2 and Metric 3.

I want to use Alteryx to generate an excel workbook with one tab for each Item No.

But I want the three Metric columns grouped by academic year.

I am struggling, at the moment I can see that if I create three cross tabs, one for each metric, then join these, then use a Table tool I can get some of the way. However in reality I have about 15 metrics so wanted to know if there was a better way. Plus doing this way the metric name is lost, when I’d like it displayed above the year in the output (shown as Table B)

Can anyone suggest an effective solution please?

In reality have have about 12 metrics, so just wanted to make sure I was following the right path before I do all of these.

From	(Product of an Altryx workflow)
A
Item Number	Year	Metirc 1	Metric 2	Metric 3
1	2009	0.5	0.7	0.9
1	2010	0.1	0.2	0.6
1	2011	0.6	0.5	0.2
1	2012	0.9	0.3	0.1
2	2009	0.5	0.4	1.3
2	2010	0.7	0.6	0.8
2	2011	0.9	0.8	1.1
2	2012	0.1	0	0.9
3	2009	0.3	0.2	1
3	2010	0.5	0.4	1.2
3	2011	0.4	0.3	1.4
3	2012	0.2	0.1	0.6
To
B	Exported to excel with each tab corresponding to a separate Item number
	Metric 1				Metric 2				Metric 3
Item Number	2009	2010	2011	2012	2009	2010	2011	2012	2009	2010	2011
1	0.5	0.1	0.6	0.9	0.7	0.2	0.5	0.3	0.9	0.6	0.2
2	0.5	0.7	0.9	0.1	0.4	0.6	0.8	0	1.3	0.8	1.1
3	0.3	0.5	0.4	0.2	0.2	0.4	0.3	0.1	1	1.2	1.4

[echuong1 · Accepted solution]

By default, there is not a way to have two rows of headers in your file (metric and year). You can concatenate these together to be one header and easily export that. I used a transpose to get all of the values in one column. I then used a formula to concatenate the metric and year values, and a cross-tab to pivot back.

To get each item on a separate tab, I used the bottom configuration in the output tool. It is not necessary to use the reporting tools unless you want a formatted report rather than a data dump.

[persevere]

Thanks so much for this awesome reply!