Turning columns into blocks of rows

tonkerthomas

Board Regular
Joined
Feb 12, 2014
Messages
56
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top