Hello Everyone, I have one input set like ABCDxyz135opq246AEFGxyz71013opq81114abc91215AHIJxyz161820ghi171921 and i wanted to convert this into : ABCDEFGHIJxyz13571013161820opq24681114 abc 91215 ghi 171921 Appreciate your help Thanks in advance !

Row To Column

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 !

Developer

Best Practices

Accepted answers

All comments

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

Workflow 305.yxmd

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.

20220530-Ektaoza9.yxzp

Ekmi9

Thank you !

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

binu_acs

@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.

Row to column workflow.yxmd

flying008

Hi, @Qiu

I've always had confusion about this option [Out fields change based on macro's configuration or data input.], when to choose it and when not to choose it? What's the difference? Could you please explain with a case ? thanks a lot !

DavidP

Hi @Ekmi9

I've had a rethink and came up with a slightly different method as shown below - I used in-workflow documentation to explain the various steps.

Row to column workflow.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703