Results 1 to 6 of 6

Multiple Columns into one Column

This is a discussion on Multiple Columns into one Column within the Excel Questions forums, part of the Question Forums category; Does anyone know how to do this? I have lots of columns that need to be stacked into a single ...

  1. #1
    Board Regular
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    81

    Default Multiple Columns into one Column

    Does anyone know how to do this? I have lots of columns that need to be stacked into a single column (I do not want to combine or concantenate)
    eg put all data in column B,
    column C, column D,column F and column E and stack them one under each other into a single column in column A. I have many many columns each with approx 500 rows of cells.All columns are in the one sheet.
    Any ideas appreciated.

  2. #2
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,318

    Default Re: Multiple Columns into one Column

    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Location
    Pakistan Karachi
    Posts
    1,099

    Default Re: Multiple Columns into one Column

    Try this

    Code:
    Sub MoveAllDataToColumnA()
        Dim i As Long, ws As Worksheet, rngCopy As Range, rngEnd As Range
        Set ws = ActiveSheet
        Do Until ws.Cells(1, 2).Value = ""
            Set rngCopy = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp))
            Set rngEnd = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0)
            rngEnd.Resize(rngCopy.Rows.Count, 1).Value = rngCopy.Value
            rngCopy.EntireColumn.Delete
        Loop
    End Sub
    "Something is better than nothing"
    "Optimisim is the key of success"
    _______________________________
    Best Regards
    Ayaz Akhtar

  4. #4
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,660

    Default Re: Multiple Columns into one Column

    Quote Originally Posted by sanrv1f View Post
    Sankar,

    I think the OP is looking for the opposite...


    http://www.cpearson.com/EXCEL/TableToColumn.aspx


    Using VBA:

    Sheet1

    BCDE
    1Col 1Col 2Col 3Col 4
    2ABCDEFGHIJKL
    3MONPQRSTUVWX
    4YZABCDEFGGIJ
    5KLMNOPQRSTUV
    6WXYZABCDEFGH
    Excel 2003



    Sheet1

    A
    1Single Column
    2ABC
    3DEF
    4GHI
    5JKL
    6MON
    7PQR
    8STU
    9VWX
    10YZA
    11BCD
    12EFG
    13GIJ
    14KLM
    15NOP
    16QRS
    17TUV
    18WXY
    19ZAB
    20CDE
    21FGH
    Excel 2003



    Code:
    Sub TableToColumn()
        Dim Rng As Range, LR As Long, i As Long
        LR = Range("B" & Rows.Count).End(xlUp).Row
        For i = 2 To LR
            Set Rng = Range("B" & i, "E" & i) 'Change range to suit needs
            Range("A" & Rows.Count).End(xlUp)(2).Resize(Rng.Count) = Application.WorksheetFunction.Transpose(Rng)
        Next i
    End Sub
    Last edited by Sandeep Warrier; Jan 28th, 2010 at 03:02 AM.
    Regards,
    Sandeep


    You can post sample data using any one of the following ways:

    1. Richard Schollar's HTML Maker
    2. Excel Jeanie
    3. Border Copy Paste

    Use code tags [CODE]'Your Code[/CODE]
    for posting codes.

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Location
    ِAmman Jordan
    Posts
    644

    Default Re: Multiple Columns into one Column

    You can do it with this formula
    ABCDEF
    1R1, C1R1, C2R1, C3R1, C4R1, C1
    2R2, C1R2, C2R2, C3R2, C4R2, C1
    3R3, C1R3, C2R3, C3R3, C4R3, C1
    4R4, C1R4, C2R4, C3R4, C4R4, C1
    5R5, C1R5, C2R5, C3R5, C4R5, C1
    6R1, C2
    7R2, C2
    8R3, C2
    9R4, C2
    10R5, C2
    11R1, C3
    12R2, C3
    13R3, C3
    14R4, C3
    15R5, C3
    16R1, C4
    17R2, C4
    18R3, C4
    19R4, C4
    20R5, C4

    Worksheet Formulas
    CellFormula
    F1=OFFSET($A$1,
    MOD(ROW()-ROW($F$1),ROWS($A$1:$A$5)),
    TRUNC((ROW()-ROW($F$1))/ROWS($A$1:$A$5)),1,1
    )



    and this formula from www.cpearson.com
    Regards
    Last edited by Yahya; Jan 28th, 2010 at 04:19 AM.

  6. #6
    Board Regular
    Join Date
    Dec 2009
    Location
    Australia
    Posts
    81

    Default Re: Multiple Columns into one Column

    Thanks Sandeep, works perfectly!

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