Separate every nth rows and transpose all rows together to columns next to each other

mlu2422

Hi Community,

I need help on separating rows and transposing them to columns next to each other.

What I have - every 6 repetitive rows:

Jurisdiction Birmingham

State AL

Entity A

Sales 820.26

Exempt 155.19

Total Sales 975.45

Jurisdiction Birmingham

State AL

Entity B

Sales 158.01

Exempt 106.14

Total Sales 264.15

The output that I want:

Jurisdiction Birmingham Jurisdiction Birmingham

State AL State AL

Entity A Entity B

Sales 820.26 Sales 158.01

Exempt 155.19 Exempt 106.14

Total Sales 975.45 Total Sales 264.15

I have tried using the Record ID tool to give the rows sequence first and then separate them out, but couldn't get to the layout that I wanted. Much appreciated if anyone can help me with this! Thanks!

Transformation

Accepted answers

Claje

Hi,

I've attached a workflow which should do exactly what you're looking for.

Take a look and let me know if you have any questions!

claje_JurisdictionCrosstab.yxmd

All comments

Claje

Hi,

Try using the following formula in a Multi-Row Formula tool creating a new field called RecordID

IF [Field1] = 'Jurisdiction' THEN [Row-1:RecordID] +1 ELSE [Row-1:RecordID] ENDIF

Not knowing your field names, you may have to change [Field1] in the above example.

Hope this helps!

mlu2422

Hi,

I actually used ceil([Field 1])/6 to get to the same results with your formula above, but do you have an idea of how to transpose those with the same RecordIDs into columns instead of stacking them on top of each other?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703