Union columns within same table and create a new field for each column value

[Jevish]

I am stuck with a problem. Need help with union multiple columns within a same table and then create a new column which will show which column name of the union data. I tried transpose, arrange and union tool but am not getting the results. I have attached the sample raw data and expected output data.

Can someone please help out !

As you can see in the attachment raw input data tab , I need to union the first amount, second amount , third amount, fourth amount and fifth amount into one column and then create another column which will tell which amount corresponds to which column. I need to repeat it for all the remaining columns which have same data (highlighted in the different colors).

Please find the attached file with expected output. Please advise or suggest a way to do this in Alteryx.

Thank You

[Jevish]

Iam able to do for one column using arrange tool but i have more than 20 columns with same repeating info 5 times..Is there any other efficient way to do this ?

[dougperez]

Have you tried to make a iterative macro? 

[Jevish]

No i have't used macro. Can it be done using macros? plz provide some more details so that i can try it out. thank you!

[dougperez]

Send me this second workflow please

[Jevish]

here is it ..thank you so much for helping out !

[Jevish]

still waiting for a solution on this. Please help.

Thank You.

[dougperez]

Hello Jes! I was sleeping hahahaha
I think that the best solution is to do the arrange tool 5 times then join then by the keys... 

[Jevish]

problem is that in my raw data i have 30 more columns. Have to repeat it 30 times 😞 

I have found another way, will see if it works or not. Thanks for your help!

[dougperez]

If it works, share with me!
At night (I live in Brasil) I will try again...