Row To Column

[Ekmi9]

Hello Everyone,

I have one input set like 

A	B	C	D
xyz	1	3	5
opq	2	4	6
A	E	F	G
xyz	7	10	13
opq	8	11	14
abc	9	12	15
A	H	I	J
xyz	16	18	20
ghi	17	19	21

and i wanted to convert this into :

A	B	C	D	E	F	G	H	I	J
xyz	1	3	5	7	10	13	16	18	20
opq	2	4	6	8	11	14
abc				9	12	15
ghi							17	19	21

Appreciate your help 

Thanks in advance !

[IraWatt]

Hey @Ekmi9,

Not sure how I would make this dynamic but here is one way of doing this:

A lot to explain here so if you have any questions or issues please ask :)
HTH!
Ira

[FrederikE]

Agree with what Ira said.

If you need it to be dynamic you can use the RegEx Tool, to identify the Capital letters within the rows and work with that. 

[Ekmi9]

number of row are dynamic how can we handle that ?

[Ekmi9]

I have added one tag column like

can we play with that to get the desire output  

[Qiu]

@Ekmi9 
Since you have added the Tag column so we can treat it as the "Group", which leads to a Batch Macro.

We can take the advance of the option of Data Schema Change option to allow the column to be unioned by name.
Then a typical Transpose and Cross Tab operation to group the values together.

[Ekmi9]

Thank you !

But I am not able to open this workflow. can you help me on this?

[Ekmi9]

my Alteryx ver is 2020.2

[binuacs]

@Ekmi9 Open with workflow in a notepad++ and change the version to 2020.2

[DavidP]

Hi @Ekmi9 

I was playing around with your example to see if I can do it without a macro. I nearly got there with one exception:

The 3 numbers highlighted in the results: 17,19, 21 appears in row 2 rather than row 4. 

I've added comments into every tool to explain what it does.

I'll have a think about how to resolve the highlighted issue.