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

smerrick

Active Member
Joined
Feb 10, 2009
Messages
255
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
That's over 40 million rows by reckoning and excel only has 1 million(ish)
 
Upvote 0
It's possible with PowerQuery (Get&Transform) but, as Fluff mentioned, it cannot be loaded into the sheet
 
Upvote 0
Which is still too many rows for Xl.
 
Last edited:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
example:

Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14
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:
[SIZE=1]// 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[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top