Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Convert Single Column To Multiple Columns Of X Rows

  1. #1
    Join Date
    8th December 2007
    Posts
    26

    Convert Single Column To Multiple Columns Of X Rows

    Hi All,

    I have an excel sheet with one column consisting of X rows. I want to convert this one column to N=X/25 columns.

    Thank you,

    SupraTT

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: Convert Single Column To Multiple Columns

    VB:
    Sub MoveOneToMany() 
        Dim lRow As Long 
        Dim lColCount As Long 
        Dim lRows As Long 
         
        lColCount = Columns.Count 
        lRow = Cells(Rows.Count, "A").End(xlUp).Row 
        For lRow = 25 To lRow Step 25 
            Cells(lRow, "A").Range("A1:A25").Copy _ 
            Cells(1, lColCount).End(xlToLeft).Cells(1, 2) 
        Next lRow 
         
        Range("A26:A" & lRow).Clear 
    End Sub 
    
    
    Last edited by Dave Hawley; February 24th, 2008 at 09:42.

  3. #3
    Join Date
    8th December 2007
    Posts
    26

    Re: Convert Single Column To Multiple Columns Of X Rows

    Hi Dave,

    Thank you for the code. It works, but if X is not divisible by 25, then the first column has more than 25 rows. Here is what I am trying to get;

    For example, if columna A has rows 1-35 filled with numbers 1 to 35, then after running the VBA code, column A will contain numbers 1-25, and column B will contain numbers 26-35.

    Thank you,

    SupraTT

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Convert Single Column To Multiple Columns Of X Rows

    Maybe ... in B1 and copy across and down,

    =IF(ROW() + 25*(COLUMN()-2) > COUNTA($A:$A), "", INDEX($A:$A, ROW() + 25*(COLUMN() - 2) ) )

    Then copy B1:X25 (or wherever the data ends), paste values, and delete column A.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

  6. #6
    Join Date
    8th December 2007
    Posts
    26

    Re: Convert Single Column To Multiple Columns Of X Rows

    Hi Dave,

    That works great. Thank you again.

    SupraTT

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Move Multiple Columns To Single Column
    By catkendig in forum EXCEL HELP
    Replies: 11
    Last Post: December 25th, 2012, 04:26
  2. Split Single Column Into Multiple Columns And Rows
    By Steve_JR in forum EXCEL HELP
    Replies: 5
    Last Post: February 1st, 2011, 04:03
  3. Convert Multiple Columns To Single Rows
    By Ryalut in forum EXCEL HELP
    Replies: 6
    Last Post: June 29th, 2010, 06:46
  4. Replies: 7
    Last Post: May 11th, 2009, 04:53
  5. Split Single Column Into Multiple Columns
    By dclark0699 in forum EXCEL HELP
    Replies: 2
    Last Post: July 20th, 2007, 00:40

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