Union with Header Name in the output

[Alteryxexpert]

I'm Doing a union of 4 output of a Alteryx workflow and need to write it into excel file, for this I need to populate the header names of all the four data in the final output.

Is this feasible in Alteryx?

Output Data Looks like below. 4 are 4 different outputs(20+Columns) which need to be merged with column names and written to a excel file.

Item	Subproduct	2023	2024	2025	2026	2028
Office	Mouse	15	6	6	5	1
Office	Keyboard	15	6	6	5	1
Office	Monitor	15	6	6	5	1
Furn	Chair	15	6	6	5	1
Furn	Table	15	6	6	5	1
Item1	Subproduct	2023	2024	2025	2026	2028
Office	Mouse	15	6	6	5	1
Office	Keyboard	15	6	6	5	1
Office	Monitor	15	6	6	5	1
Furn	Chair	15	6	6	5	1
Furn	Table	15	6	6	5	1
Item2	Subproduct	2023	2024	2025	2026	2028
Office	Mouse	15	6	6	5	1
Office	Keyboard	15	6	6	5	1
Office	Monitor	15	6	6	5	1
Furn	Chair	15	6	6	5	1
Furn	Table	15	6	6	5	1
Item3	Subproduct	2023	2024	2025	2026	2028
Office	Mouse	15	6	6	5	1
Office	Keyboard	15	6	6	5	1
Office	Monitor	15	6	6	5	1
Furn	Chair	15	6	6	5	1
Furn	Table	15	6	6	5	1

[Qiu]

@Alteryxexpert 
Maybe we can try the option of "Manually Configure Fields" in the Union tool as below.

[Rhys_Cooper]

Hi @Alteryxexpert  thank you for your question. I have attatched a workflow with my solution with a small example using two sheets you want to union along with the headers. It is a dynamic solution such that you should be able to expand this to as many sheets you have, especially if you package it into a batch macro ( can provide help on this too if needed). I have done it for stacking the same sheet for simplicity hence the repeated numbers in the output, natrually you would replace this with 4 input streams.

Steps per sheet:

1. extract the headers using the fields info tool, cross tab to get them horizontal again

2. union the headers (to be used at the top of the next sheet) to the bottom of the prior sheet

Steps for unioned sheets:

1. union all these processed sheets on top of eachother

2. add a field using the append fields tool to track which number it is in the series.

3. use the multirow formula tool to calculate each sheets appropriate series number

Dynamically renaming the Item field header to reflect the series number

1. use the formula tool to append the series number from the "series identifier" column

2. drop the series indentifer column as it is no longer needed