Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Turn columns into rows from on sheet to another

    On Sheet1 there are several hundred rows. Row 1 is a column header row.
    In columns F, H, J, L, N, P are data I want to copy and turn into 6 rows for each row (starting with row 2) of Sheet1 and paste the new set of rows into Sheet2.

    For example, if there are 101 rows in Sheet1 (counting the header row), Sheet2 will now have 600 rows (and 1 column).

    Does that make sense? Macro, I suspect.
    Last edited by kweaver; 2013-12-07 at 04:00.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach an example, I do not completely understand your setup or what you want...

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I resolved this with a formula using INDIRECT and (2+INT((ROW()-1)/6)) to pick the rows.

    =INDIRECT("Sheet1!F"&(2+INT((ROW()-1)/6))) then, changed F to H, etc. and filled the block of 6 down.

    Any better ideas would, of course, be appreciated.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    KW,

    Here is a VBA approach that will take the values from each column that you specified on each row and transpose them vertically onto sheet 2 in column A; each value having its own row. Hope this is what you are looking for.

    Maud

    transpose1.png Transpose2.png

    Code:
    Public Sub TransposeRows()
    With Worksheets("Sheet2")
    LastRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row
    Row = 2
    For I = 2 To LastRow
        For J = 6 To 16 Step 2
            .Cells(Row, 1) = Cells(I, J)
            Row = Row + 1
        Next J
    Next I
    End With
    End Sub
    Although the images do no show it, the code is adjusted to include values in column F
    Last edited by Maudibe; 2013-12-07 at 12:47.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thanks, Maud. What's your feeling about my code versus the VBA approach?

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    FWIW...My general opinion is if it works all's good! That said the VBA has the advantage (with some minor changes, adding arguments and/or prompts for source/dest addresses) of being stored in your Personal.xls file and then can be used on any worksheet with out having to recreate a complex formula in a new environment. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    I just prefer VBA because it is too easy to mistakenly overwrite a formula which can bring down the whole spread sheet so there is no need to lock down the worksheet. VBA is so much more versatile and easier to trouble shoot particularly when many formulas are embedded within formulas. You can make things happen when you want them to occur as well as not making them occur when you don't want them to.

    In your instance, if that is all you will be using the cell for, you can guarantee that it will not be overwritten, and you want it to copy the values immediately then your code is as good as any VBA script. If you will be adding or deleting rows on the source sheet then just use caution in your referencing.

    And ditto everything RG said.

    Maud

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thanks both of you for the insight.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •