Transposing to alternate columns

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Transposing to alternate columns

  1. #1
    Board Regular ChuckDrago's Avatar
    Join Date
    Sep 2007
    Location
    Long Island, NY
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transposing to alternate columns

     
    Hi everyone,

    I have a database where a number of rows of a single column need to be copied (transposed) to a single row and alternating columns. For example, if my data was in A1:A10, what I need is to copy it to a different worksheet into columns A1, C1, E1, ...S1.
    Before I engage into building a convoluted workaround, I wonder if anyone knows a simpler formula?
    As usual, most appreciative.
    Chuck

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,882
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transposing to alternate columns

    Not a formula, but a simple macro. Assuming a dynamic range in column A.
    Code:
    Sub t()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Set sh1 = Sheets(1) 'Edit sheet name
    Set sh2 = Sheets(2) 'Edit sheet name
        For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
            If c <> "" Then
                If sh2.Cells(1, 1) = "" Then
                    c.Copy sh2.Cells(1, 1)
                Else
                    c.Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 2)
                End If
            End If
        Next
    End Sub
    Last edited by JLGWhiz; Dec 7th, 2017 at 06:14 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular ChuckDrago's Avatar
    Join Date
    Sep 2007
    Location
    Long Island, NY
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transposing to alternate columns

    Quote Originally Posted by JLGWhiz View Post
    Not a formula, but a simple macro. Assuming a dynamic range in column A.
    Code:
    Sub t()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Set sh1 = Sheets(1) 'Edit sheet name
    Set sh2 = Sheets(2) 'Edit sheet name
        For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
            If c <> "" Then
                If sh2.Cells(1, 1) = "" Then
                    c.Copy sh2.Cells(1, 1)
                Else
                    c.Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 2)
                End If
            End If
        Next
    End Sub
    Thank you JLGWhiz! I had coded my loops combo with incremental indexes but your version is more economical, more elegant I might add. It works as I needed, it is already adopted and I remain very grateful for it.
    Chuck

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,882
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transposing to alternate columns

      
    Quote Originally Posted by ChuckDrago View Post
    Thank you JLGWhiz! I had coded my loops combo with incremental indexes but your version is more economical, more elegant I might add. It works as I needed, it is already adopted and I remain very grateful for it.
    Chuck
    You're welcome,
    regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com