How to combine multiple columns into one list in Excel?
To combine or merge multiple columns into one long list, normally, you can copy and paste the columns data one by one into the specified column. But, here, I can introduce you some quick tricks to solve this task in Excel.
Without repeating to copy and paste the columns data, the following VBA code may help you to deal with this job quickly and easily.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
VBA code: Combine or merge multiple columns into one list
Sub CombineColumns1() 'updateby Extendoffice Dim xRng As Range Dim i As Integer Dim xLastRow As Integer Dim xTxt As String On Error Resume Next xTxt = Application.ActiveWindow.RangeSelection.Address Set xRng = Application.InputBox("please select the data range", "Kutools for Excel", xTxt, , , , , 8) If xRng Is Nothing Then Exit Sub xLastRow = xRng.Columns(1).Rows.Count + 1 For i = 2 To xRng.Columns.Count Range(xRng.Cells(1, i), xRng.Cells(xRng.Columns(i).Rows.Count, i)).Cut ActiveSheet.Paste Destination:=xRng.Cells(xLastRow, 1) xLastRow = xLastRow + xRng.Columns(i).Rows.Count Next End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to remind you select the data range that you want to combine into only one column, see screenshot:
4. And then click OK button, and the selected columns have been merged into only one column as following screenshots shown:
Tips: After running this VBA, the original data of the range will be cleared, you’d better copy and save them in another location first.
If you have installed Kutools for Excel, its Transform Range utility can help you combine multiple columns into one single column or single row.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:
1. Select the columns data that you want to combine into one list.
2. Then click Kutools > Range > Transform Range, see screenshot:
3. In the Transform Range dialog box, select Range to single column option, and click Ok button, see screenshot:
4. In the following popped out Transform Range box, click a cell where you want to output the result, see screenshot:
5. Then click OK button, your selected columns have been combined into one single column in the specified location you desired. See screenshot: