How to Consolidate Data from Multiple Excel Columns All into One Column

Question

Lets say I have an excel sheet with 4 columns of data  amp  20 000 rows of data in each column   What is the most efficient way to get it so that I have all of that data consolidated into one column  I E  - 80 000 rows of data in column A instead of 20 000 rows of data spread out across 4 columns    Also  how to implement that solution   What I mean is  if your solution isn t a  formula  but VBA  how do I implement that solution   Thanks

User · Answer

The formula   OFFSET Sheet1  A 1 MOD ROW  -1 COUNT Sheet1  A 1  A 20000         ROW  -1  COUNT Sheet1  A 1  A 20000     placed into each cell of your second workbook will retrieve the appropriate cell from the source sheet  No macros  simple copying from one sheet to another to reformat the results   You will need to modify the ranges in the COUNT function to match the maximum number of rows in the source sheet  Adjust for column headers as required   If you need something other than a 0 for empty cells  you may prefer to include a conditional   A script to reformat the data may well be more efficient  but 20k rows is no longer a real limit in a modern Excel workbook

User · Answer

You didn t mention if you are using Excel 2003 or 2007  but you may run into an issue with the   of rows in Excel 2003 being capped at 65 536  If you are using 2007  the limit is 1 048 576    Also  can I ask what your end goal is for your analysis  If you need to perform many statistical calculations on your data  I would recommend moving out of the Excel environment into something that is more directly suited for data manipulation and analysis  such as R   There are a variety of options for connecting R to Excel  including   RExcel RODBC Other options in the R manual   Regardless of what you choose to use to move data in out of R  the code to change from wide to long format is pretty trivial  I enjoy the melt   function from the reshape package  That code would look like   library reshape   Fake data  4 columns  20k rows df  lt - data frame foo   rnorm 20000        bar   rlnorm 20000        fee   rnorm 20000        fie   rlnorm 20000     Create new object with 1 column  80k rows df m  lt - melt df    From there  you can perform any number of statistical or graphing operations  If you use the RExcel plugin above  you can fire all of this up and run it within Excel itself  The R community is very active and can help address any and all questions you may encounter   Good luck

User · Answer

Here is how you do it with some simple Excel formulae  and no fancy VBA needed  The trick is to use the OFFSET formula  Please see this example spreadsheet   https   docs google com spreadsheet ccc key 0AuSyDFZlcRtHdGJOSnFwREotRzFfM28tWElpZ1FaR2c amp usp sharing gid 0

User · Answer

Take a look at Blockspring - you do need to install the plugin  but then it s just another function you call like this    BLOCKSPRING  twodee-array-reduce   input array  D5 F7    The source code and other details are here  If this doesn t suit and or you want to build off my solution  you can fork my function  Python  or use another supported scripting language  Ruby  R  JS  etc

User · Answer

Best and Simple solution to follow   Select the range of the columns you want to be copied to single column  Copy the range of cells  multiple columns   Open Notepad     Paste the selected range of cells  Press Ctrl H  replace  t by  n and click on replace all  all the multiple columns fall under one single column  now copy the same and paste in excel  Simple and effective solution for those who dont want to waste time coding in VBA

User · Answer

Save your workbook   If this code doesn t do what you want  the only way to go back is to close without saving and reopen   Select the data you want to list in one column  Must be contiguous columns   May contain blank cells   Press Alt F11 to open the VBE  Press Control R to view the Project Explorer  Navigate to the project for your workbook and choose Insert - Module  Paste this code in the code pane  Sub MakeOneColumn        Dim vaCells As Variant     Dim vOutput   As Variant     Dim i As Long  j As Long     Dim lRow As Long      If TypeName Selection     Range  Then         If Selection Count  gt  1 Then             If Selection Count  lt   Selection Parent Rows Count Then                 vaCells   Selection Value                  ReDim vOutput 1 To UBound vaCells  1    UBound vaCells  2   1 To 1                   For j   LBound vaCells  2  To UBound vaCells  2                      For i   LBound vaCells  1  To UBound vaCells  1                          If Len vaCells i  j    gt  0 Then                             lRow   lRow   1                             vOutput lRow  1    vaCells i  j                          End If                     Next i                 Next j                  Selection ClearContents                 Selection Cells 1  Resize lRow  Value   vOutput             End If         End If     End If  End Sub   Press F5 to run the code

[excel] How to Consolidate Data from Multiple Excel Columns All into One Column

Examples related to excel

Examples related to vba