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:
<tbody>
</tbody>
... to this:
<tbody>
</tbody>
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
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:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |
| 1 | M1 | M2 | M3 | M4 | M5 | M6 | T11 | T12 | T13 | T14 | T15 | T16 | T21 | T22 | T23 | T24 | T25 | T26 | T31 | T32 | T33 | T34 | T35 | T36 |
| 2 | M1 | M2 | M3 | M4 | M5 | M6 | T11 | T12 | T13 | T14 | T15 | T16 | ||||||||||||
| 3 | M1 | M2 | M3 | M4 | M5 | M6 | T11 | T12 | T13 | T14 | T15 | T16 | T21 | T22 | T23 | T24 | T25 | T26 |
<tbody>
</tbody>
... to this:
| A | B | C | D | E | F | G | H | I | J | K | L | |
| 1 | M1 | M2 | M3 | M4 | M5 | M6 | T11 | T12 | T13 | T14 | T15 | T16 |
| 2 | T21 | T22 | T23 | T24 | T25 | T26 | ||||||
| 3 | T31 | T32 | T33 | T34 | T35 | T36 | ||||||
| 4 | M1 | M2 | M3 | M4 | M5 | M6 | T11 | T12 | T13 | T14 | T15 | T16 |
| 5 | M1 | M2 | M3 | M4 | M5 | M6 | T11 | T12 | T13 | T14 | T15 | T16 |
| 6 | T21 | T22 | T23 | T24 | T25 | T26 |
<tbody>
</tbody>
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
