Turning columns into blocks of rows

[tonkerthomas]

Good afternoon everybody.

I have a very large spreadsheet which contains around 2000 rows, the first six columns of which are master data. Thereafter, there is a number of six-column blocks that contain "sets" of transactional data. All rows have at least one six-column block, some have more, up to a maximum of 87 blocks (meaning 6*87 columns). Beyond the point at which any given row "runs out" of data, it's completely empty.

What I need to do is this: for any row which has more than one set of transactional data (i.e. any row with data in column M or beyond), I need to cut the data out, in six-column blocks, and paste that into new rows beneath the first set of transactional data.

So, we'd go from this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[TD]T11[/TD]
[TD]T12[/TD]
[TD]T13[/TD]
[TD]T14[/TD]
[TD]T15[/TD]
[TD]T16[/TD]
[TD]T21[/TD]
[TD]T22[/TD]
[TD]T23[/TD]
[TD]T24[/TD]
[TD]T25[/TD]
[TD]T26[/TD]
[TD]T31[/TD]
[TD]T32[/TD]
[TD]T33[/TD]
[TD]T34[/TD]
[TD]T35[/TD]
[TD]T36[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[TD]T11[/TD]
[TD]T12[/TD]
[TD]T13[/TD]
[TD]T14[/TD]
[TD]T15[/TD]
[TD]T16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[TD]T11[/TD]
[TD]T12[/TD]
[TD]T13[/TD]
[TD]T14[/TD]
[TD]T15[/TD]
[TD]T16[/TD]
[TD]T21[/TD]
[TD]T22[/TD]
[TD]T23[/TD]
[TD]T24[/TD]
[TD]T25[/TD]
[TD]T26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

... to this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[TD]T11[/TD]
[TD]T12[/TD]
[TD]T13[/TD]
[TD]T14[/TD]
[TD]T15[/TD]
[TD]T16[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T21[/TD]
[TD]T22[/TD]
[TD]T23[/TD]
[TD]T24[/TD]
[TD]T25[/TD]
[TD]T26[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T31[/TD]
[TD]T32[/TD]
[TD]T33[/TD]
[TD]T34[/TD]
[TD]T35[/TD]
[TD]T36[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[TD]T11[/TD]
[TD]T12[/TD]
[TD]T13[/TD]
[TD]T14[/TD]
[TD]T15[/TD]
[TD]T16[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]M1[/TD]
[TD]M2[/TD]
[TD]M3[/TD]
[TD]M4[/TD]
[TD]M5[/TD]
[TD]M6[/TD]
[TD]T11[/TD]
[TD]T12[/TD]
[TD]T13[/TD]
[TD]T14[/TD]
[TD]T15[/TD]
[TD]T16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T21[/TD]
[TD]T22[/TD]
[TD]T23[/TD]
[TD]T24[/TD]
[TD]T25[/TD]
[TD]T26[/TD]
[/TR]
</tbody>[/TABLE]


As you can see, the master data doesn't need to be copied down into the new rows, and once the transactional data "runs out" for any given line, I can stop inserting rows and cutting the data into them (so there won't be any empty rows in the final data).

Can anybody help me? I suspect this isn't terribly hard, but even though I think I understand the logic required I can't convert into into a macro.

My thanks, as ever, to anybody who has taken the time and trouble to read this, and in advance to anybody who has a crack at it.

Cheers

Jeff

 

[MickG]

No problem , Glad you have it sorted !!