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!

[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?

[Claje · Accepted solution]

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!

[mlu2422]

It worked. Thank you!