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 & 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!

why not just copy/paste? if you need to make it in multiple sheet, just record a macro of the copy/pasting — Eric Fortis
Because I can have up to 30 or 40 columns with all of them having 20 to 70 thousand entries. Copy & pasting becomes quite tedious...having to find where each columns data stops, clicking it, scrolling back to top, selecting all, copying, going back to the bottom of column A, pasting...repeat 30 or 40 times. — Learning
I'm not sure how often you do this - but what I would do is export the file to a CSV and use a program like Notepad++ to find and replace all commas with \n new line characters (or whatever character your CSV uses to end the line). Save the file and re-open in excel and it should have all of your data in one column. — jeremysawesome
With so much data I am curious to know how long a macro will take to do the work. Could you report how long it take to run the macro? — momobo
possible duplicate of Excel - Combine multiple columns into one column — user1825083

Dick Kusleika Dick Kusleika · Accepted Answer · 2010-12-18T23:20:20

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 > 1 Then
            If Selection.Count <= 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)) > 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

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

6 Answers