How to convert lots of columns of data all into just one column easily?
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: How to convert lots of columns of data all into just one column easily?
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,824
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    Quote Originally Posted by smerrick View Post
    Hello,

    I do not think I have been clear with my request. As a simple example, imagine C3:C7 has random data. In addition, there is other random data in cells D3:D7, plus E3:E7 and so on. What I would like to do in column A is to have the data from C3:C7 into A3:A7, the data from D3:D7 into A8:A12 (i.e. underneath), the data form E3:E7 into A13:A17 and so on.

    Is this at all doable?

    Thanks
    Not if you have 2495 rows by 622 columns, it's too much to fit into 1 column
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to convert lots of columns of data all into just one column easily?

    If I understood well OP has 5 rows for each 624 columns (result 3120 rows) but maybe I am wrong
    Last edited by sandy666; Jul 26th, 2019 at 08:12 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #13
    Board Regular
    Join Date
    Feb 2009
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    Hi,

    Yes the example you have (aimded at Sandy666) is exactly what I am trying to find the solution for, thank you for taking the time to look into it for me.

    With the code, I simply copied it when recording a macro but it does not seem to work for me?
    Last edited by smerrick; Jul 26th, 2019 at 08:15 AM.

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,824
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    Quote Originally Posted by sandy666 View Post
    If I understood well OP has 5 rows for each 624 columns (result 3120 rows) but maybe I am wrong
    I'm going by
    data starts at row 6 and finishes at to 2,500.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    as mentioned in post #3 this is PowerQuery (Get&Transform) NOT vba
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #16
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    Quote Originally Posted by Fluff View Post
    I'm going by
    right

    good description is a half success
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #17
    Board Regular
    Join Date
    Feb 2009
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    OK thanks. I have not come across this feature before, is there an alternative way as opposed to using this?

    Quote Originally Posted by sandy666 View Post
    as mentioned in post #3 this is PowerQuery (Get&Transform) NOT vba

  8. #18
    Board Regular
    Join Date
    Jul 2011
    Posts
    475
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    As others have said, it sounds like you have too much data to put into one column but you are welcome to give this a try:

    Code:
    Sub combineCols()
        Dim r As Range
        Dim a(), b()
        Dim lastRow As Long, totalValues As Long, i As Long, j As Long, k As Long
        
        lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
        Set r = Range("A6", .Cells(lastRow, "WZ"))
        totalValues = Application.CountA(r)
        If totalValues > Rows.Count Then
            MsgBox "Can't be done. Not enough rows!!!", vbCritical
        Else
            k = 0
            ReDim b(1 To totalValues, 1 To 1)
            For j = 1 To UBound(a, 2)
                For i = 1 To UBound(a)
                    If LenB(a(i, j)) Then
                        k = k + 1
                        b(k, 1) = a(i, j)
                    End If
                Next j
            Next j
            Workbooks.Add.Sheets(1).Range("A1").Resize(totalValues).Value = b
            Erase a
            Erase b
        End If
        Set r = Nothing
    End Sub
    edit: You could export to a csv I suppose, but excel wouldn't open it.
    Last edited by trunten; Jul 26th, 2019 at 08:24 AM.

  9. #19
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    Quote Originally Posted by smerrick View Post
    is there an alternative way as opposed to using this?
    I don't know, maybe someone else
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #20
    Board Regular
    Join Date
    Feb 2009
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to convert lots of columns of data all into just one column easily?

    Thanks for the code. I have tried this but get 'compile error invalid or unqualified reference' at the .cells part of the code?


    Quote Originally Posted by trunten View Post
    As others have said, it sounds like you have too much data to put into one column but you are welcome to give this a try:

    Code:
    Sub combineCols()
        Dim r As Range
        Dim a(), b()
        Dim lastRow As Long, totalValues As Long, i As Long, j As Long, k As Long
        
        lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
        Set r = Range("A6", .Cells(lastRow, "WZ"))
        totalValues = Application.CountA(r)
        If totalValues > Rows.Count Then
            MsgBox "Can't be done. Not enough rows!!!", vbCritical
        Else
            k = 0
            ReDim b(1 To totalValues, 1 To 1)
            For j = 1 To UBound(a, 2)
                For i = 1 To UBound(a)
                    If LenB(a(i, j)) Then
                        k = k + 1
                        b(k, 1) = a(i, j)
                    End If
                Next j
            Next j
            Workbooks.Add.Sheets(1).Range("A1").Resize(totalValues).Value = b
            Erase a
            Erase b
        End If
        Set r = Nothing
    End Sub
    edit: You could export to a csv I suppose, but excel wouldn't open it.

Some videos you may like

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
  •