+ Reply to Thread
Results 1 to 4 of 4

Transpose 2 Columns into Multiple Columns per Range of Cells

Hybrid View

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    Near Tampa, Florida
    MS-Off Ver
    | Work: 2007 | Home: 2013 |
    Posts
    25

    Transpose 2 Columns into Multiple Columns per Range of Cells

    I have an excel with two columns and 183137 rows. Column A is an Identifier for Column B.
    On Page Break Preview it is set to 51 rows per page. How can I transpose the two columns into multiple columns?

    E.G. Rows 1-51 is Columns A and B. Rows 52-101 is Columns C and D. Rows 102-152 is Columns E and F. etc.

    Basically, I want to convert two extremely long columns into multiple columns so I can fit all the data onto less pages to print. Instead of a 3600+ pages with two columns, I would prefer 720 pages with 5 sets of the two columns. I can explain further if necessary.


    Here is an Example.
    Example.xlsm

    Thank you for taking a look. Everyone on this forum is wonderful. I've learned a lot, though clearly not everything!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Transpose 2 Columns into Multiple Columns per Range of Cells

    You may have to tinker with this to get it to where you want, but basically...

    In D1 copied to the right >> =INDEX(A:A,ROW(A2))

    Now grad D1 and E1 and drag down until row 51

    Now just repeat for the other columns...

    If you want to stick with 51 rows per column, in F1 copied to the right >> =INDEX($A:$A,ROW(A53))

    Now grad F1 and G1 and drag down until row 51

    Continue this until you have the right porportions setup...
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Transpose 2 Columns into Multiple Columns per Range of Cells

    Try the attached. I may need to re-adjust the code for the first row.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-03-2012
    Location
    Near Tampa, Florida
    MS-Off Ver
    | Work: 2007 | Home: 2013 |
    Posts
    25

    Re: Transpose 2 Columns into Multiple Columns per Range of Cells

    Thank you, AB33! Many thanks.

    This worked beautifully. I especially love how it left my original sheet undisturbed.
    Sub copyme1()
    Application.ScreenUpdating = 0
    Dim LR&, i&, ms As Worksheet
    Set ms = Sheets("Result")
    NC = 1
     With Sheets("appz")
     
        LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        For i = 2 To LR Step 51
          .Cells(i, 1).Resize(51, 2).Copy
           ms.Cells(1, NC).PasteSpecial xlValues
           NC = NC + 2
        Next i
     End With
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macros to transpose from multiple columns to selected columns and maintaining cell format
    By rrajnish in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2013, 01:45 PM
  2. Transpose array with multiple columns per 1 row
    By alxw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 11:47 PM
  3. Transpose a row into multiple columns- please help!
    By brittleigh22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2013, 12:02 AM
  4. [SOLVED] Transpose Variable Amount of Columns in Range
    By Ronjay24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 04:25 PM
  5. transpose multiple rows and columns
    By prawer in forum Excel General
    Replies: 2
    Last Post: 06-03-2009, 04:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1