none
From one columns of dates to 100 colums RRS feed

  • Question

  • Hello,

    I have a column of numbers ( B ). In this column I want to take the first 720 values and put them in column G, the following 720 values in column H and so on up to create 100 parallel columns. Thanks  for the advice.

                    B                                       G        H    ...

    1              8                                       8       22

    2              4                                       4        ...

    3              89                                     89

    ...                                                      ...

    720         56                                     56

    721         22

    ...

    72011      5

    Thursday, May 21, 2015 5:10 PM

All replies

  • Re:  72000 rows to 100 columns

    Sub DivideAndConquer()
     Dim R As Long
     Dim C As Long
     
     C = 7
     For R = 1 To 72000 Step 720
       Cells(R, 1).Resize(720, 1).Cut Destination:=Cells(1, C)
       C = C + 1
     Next
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:20 PM
    Thursday, May 21, 2015 6:41 PM
  • Thank you. The macro works fine. Now I try to adapt it for my specific application. Could you please tell me the meaning of the letter R and C ? And C=7 ?  

    Tuesday, May 26, 2015 5:44 PM
  • Ok, I understand the meaning of letter R and C. Because I want repeat the function for other 2 columns I have tried to repeat the code in this way but I have to run 3 different macro:

    ---------------------------------------------------------------------

    Sub DivideAndConquer()
     Dim R As Long
     Dim C As Long
     
     C = 7
     For R = 6 To 72005 Step 720
       Cells(R, 2).Resize(720, 1).Cut Destination:=Cells(6, C)
       C = C + 1
     Next
     End Sub
     
     Sub DivideAndConquer2()
     Dim R As Long
     Dim C As Long
     
     C = 7
     For R = 6 To 72005 Step 720
       Cells(R, 3).Resize(720, 1).Cut Destination:=Cells(728, C)
       C = C + 1
     Next
    End Sub


    Sub DivideAndConquer3()
     Dim R As Long
     Dim C As Long
     
     C = 7
     For R = 6 To 72005 Step 720
       Cells(R, 4).Resize(720, 1).Cut Destination:=Cells(1450, C)
       C = C + 1
     Next
    End Sub

    -----------------------------------------------------------------------

    I want to directly paste the 3 columns with 72000 values and automatically split them into 100 columns each

    Wednesday, May 27, 2015 11:48 AM
  • Run 3 macros sequentially

    '---
    Sub DivideAndConquer()
      Dim R As Long
      Dim C As Long
     
      C = 7
      For R = 6 To 72005 Step 720
        Cells(R, 2).Resize(720, 1).Cut Destination:=Cells(6, C)
        C = C + 1
      Next
      Call DivideAndConquer2     '<<<<
      Call DivideAndConquer3     '<<<<
    End Sub

    '---
    Jim Cone
    Thursday, May 28, 2015 3:04 PM
  • Thank you
    Thursday, July 16, 2015 3:47 PM