Hi community, I can't quite figure out what should be a relatively simple transformation. My data is structured like this. GroupPerson1A2B3C4D1E4F3G I want the output to be 1234ABCDE GF I thought about cross-tabbing using a concatenate function and then doing "text to rows". But in practice there are a lot of groups, not just 4, so I was hoping there was a more practical solution. Thanks!

How to rearrange data

Hi community,

I can't quite figure out what should be a relatively simple transformation. My data is structured like this.

Group	Person
1	A
2	B
3	C
4	D
1	E
4	F
3	G

I want the output to be

1	2	3	4
A	B	C	D
E		G	F

I thought about cross-tabbing using a concatenate function and then doing "text to rows". But in practice there are a lot of groups, not just 4, so I was hoping there was a more practical solution.

Thanks!

Transformation

Accepted answers

BrandonB

You will want to use a multi row tool to make kind of like a record ID within each group. Then when you crosstab you can group by this new field and it will rearrange the data as you are looking for. Workflow is attached.

rearrange 1.png

rearrange 2.png

Rearrange.yxmd

All comments

AngelosPachis

Hi @air0storm

First of all, that's one great username. Secondly, I've mocked up a workflow that answers your question, you just have to create a field that you will group on, before you cross-tab