Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

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

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

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

    Hello,

    Thanks for reading.

    I have columns of data from column C right through to column WZZ. What I want to do is to put all of this data into column A only. Each column has the exact same amount of rows - data starts at row 6 and finishes at to 2,500.

    Is there a qucik way to do this?


    Kind Regards

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    That's over 40 million rows by reckoning and excel only has 1 million(ish)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

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

    It's possible with PowerQuery (Get&Transform) but, as Fluff mentioned, it cannot be loaded into the sheet
    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
    I don't use vba in any form!

  4. #4
    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?

    Sorry, was meant to state to columns WZ

    Quote Originally Posted by Fluff View Post
    That's over 40 million rows by reckoning and excel only has 1 million(ish)

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Which is still too many rows for Xl.
    Last edited by Fluff; Jul 26th, 2019 at 07:47 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 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
    That's over 40 million rows by reckoning and excel only has 1 million(ish)
    ?? The OP wrote that there are 2,495 rows (2,500-5)

    Have a look here the UDF might help :
    https://trumpexcel.com/concatenate-excel-ranges/

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

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

    still limited (624 x 2500 ->> ca. 1 500 000 rows)

    Total number of rows and columns on a worksheet: 1,048,576 rows by 16,384 columns
    Last edited by sandy666; Jul 26th, 2019 at 07:50 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
    I don't use vba in any form!

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Quote Originally Posted by footoo View Post
    ?? The OP wrote that there are 2,495 rows (2,500-5)

    Have a look here the UDF might help :
    https://trumpexcel.com/concatenate-excel-ranges/
    I read it as move the columns into 1 column, rather than concatenate all the columns.

    We need more info from the OP.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    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?

    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

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

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

    example:

    Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 Column11 Column12 Column13 Column14
    0.774915096
    0.4188964
    0.748101
    0.0113204
    0.234089
    0.3726273
    0.5000491
    0.1903539
    0.338207
    0.33014443
    0.2866486
    0.59869727
    0.74850883
    0.45926496
    0.655775748
    0.2545142
    0.8985789
    0.4760538
    0.4180566
    0.2394964
    0.2810331
    0.9367432
    0.8482066
    0.03713009
    0.20646736
    0.29743577
    0.44274266
    0.81103977
    0.50063972
    0.2947792
    0.1497255
    0.8233949
    0.5296256
    0.3779214
    0.4620415
    0.3396098
    0.2308224
    0.04553053
    0.1073528
    0.36171733
    0.31244143
    0.96539522
    0.246439217
    0.7244636
    0.5658322
    0.6153575
    0.6659154
    0.5757108
    0.3281969
    0.7401593
    0.4468049
    0.74536467
    0.53305058
    0.19628399
    0.5024499
    0.77802828
    0.484824378
    0.3929827
    0.611989
    0.5418079
    0.8376284
    0.3881836
    0.0115521
    0.7541895
    0.1627849
    0.53843126
    0.11058122
    0.10299539
    0.28047611
    0.62218031
    0.259047207
    0.6760645
    0.1959383
    0.8026555
    0.2681285
    0.2375676
    0.6194748
    0.5299913
    0.4682507
    0.74324482
    0.54389424
    0.19041906
    0.33360572
    0.91848514
    0.664386926
    0.9470563
    0.3502736
    0.4716219
    0.4532153
    0.8834114
    0.264256
    0.2326071
    0.2286822
    0.69401199
    0.27611594
    0.46512063
    0.09497303
    0.42726031
    Column1
    0.774915096
    0.655775748
    0.50063972
    0.246439217
    0.484824378
    0.259047207
    0.664386926
    0.418896408
    0.254514241
    0.294779202
    0.724463592
    0.39298274
    0.676064498
    0.947056257
    0.748101046
    0.898578854
    0.149725526
    0.565832209
    0.611988953
    0.195938291
    0.350273587
    0.011320355
    0.476053831
    0.823394934
    0.615357473
    0.541807915
    0.80265547
    0.471621884
    0.234089031
    0.418056628
    0.529625633
    0.665915371
    0.837628429
    0.268128491
    0.453215341
    0.372627348
    0.239496371
    0.377921395
    0.575710784
    0.388183619
    0.237567589
    0.883411371
    0.500049101
    0.281033058
    0.462041528
    0.328196945
    0.011552087
    0.619474757
    0.264256039
    0.190353896
    0.936743225
    0.339609804
    0.740159329
    0.754189537
    0.529991278
    0.232607071
    0.338206967
    0.848206569
    0.230822412
    0.446804924
    0.162784909
    0.468250709
    0.228682234
    0.330144428
    0.037130089
    0.045530526
    0.745364667
    0.538431259
    0.743244824
    0.69401199
    0.286648601
    0.206467362
    0.107352802
    0.533050576
    0.110581224
    0.543894244
    0.276115938
    0.598697267
    0.297435766
    0.361717328
    0.196283988
    0.102995395
    0.190419064
    0.46512063
    0.748508825
    0.442742664
    0.312441426
    0.502449895
    0.28047611
    0.333605721
    0.094973034
    0.459264965
    0.811039769
    0.965395218
    0.778028278
    0.622180313
    0.918485143
    0.427260308


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        T2C = Table.ToColumns(Source),
        C2T = Table.FromList(T2C, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandListColumn(C2T, "Column1")
    in
        Expand
    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
    I don't use vba in any form!

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
  •