VBA turn columns into rows

  • 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:

    ABCDEFGHIJKLMNOPQRSTUVWX
    1M1M2M3M4M5M6T11T12T13T14T15T16T21T22T23T24T25T26T31T32T33T34T35T36
    2M1M2M3M4M5M6T11T12T13T14T15T16
    3M1M2M3M4M5M6T11T12T13T14T15T16T21T22T23T24T25T26



    ... to this:

    ABCDEFGHIJKL
    1M1M2M3M4M5M6T11T12T13T14T15T16
    2T21T22T23T24T25T26
    3T31T32T33T34T35T36
    4M1M2M3M4M5M6T11T12T13T14T15T16
    5M1M2M3M4M5M6T11T12T13T14T15T16
    6T21T22T23T24T25T26



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

  • Try this. Run the macro with the data sheet active

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Just realised that if there were over 2000 rows of data and nearly all had all 87 6-column-blocks filled then the number of rows needed to rearrange the data as required could exceed the total number of rows available on a worksheet. This code includes an error trap to account for the possibility of insufficient rows to rearrange the data.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try this. Run the macro with the data sheet active


    Nailed it! I now have six thousand rows of data all perfectly parsed and ready to go. Thanks a million KjBox, I know that's a deceptively tricky thing to do and would have been well beyond me. You've made my day - and made my boss a very, happy man.


    Jeff

  • Just realised that if there were over 2000 rows of data and nearly all had all 87 6-column-blocks filled then the number of rows needed to rearrange the data as required could exceed the total number of rows available on a worksheet. This code includes an error trap to account for the possibility of insufficient rows to rearrange the data.


    That's fine - the vast majority of the rows have four data sets or fewer, and there's only one that has eighty-seven. Still, it's always good to cover the bases if you can, saves you wondering in a year's time why your perfectly functioning macro suddenly stopped working. :D

  • You're welcome.


    You are right, it is always a good idea to include error traps even if the possibility of an error occurring is remote.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi HjBox, good morning everybody,


    I've now got my next file which I've tweaked and edited to be in the same format as the last file I "rearranged"... but when I try and run the macro on it, it falls over. I get:


    Quote

    "Run-time error '9':


    Subscript out of range


    ... and when I de-bug, it's highlighting:


    [VBA]
    y(v, iii) = x(i, ii) [/VBA]


    I'm pretty sure there's something different about my data but I can't figure out what it is, nor why it would affect the macro. Any ideas why this is no longer working?


    Cheers


    Jeff

  • Okay, disaster averted. ;) I started from scratch with my data and this time, it's worked. The only thing I'm aware of doing differently is that first time round, I converted the text values in column A to numbers, and the second time I didn't. Was that why it fell over?


    Jeff

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!