4 Ways to Combine Columns into One List in Excel

If we have our desired data indifferent columns in an excel file and combine them in a list. In that case we can cut and paste them into the desired column but this process is feasible when dataset is small. But if you want to work with big dataset this process becomes very complicated. So in this article we are going to show you 4 ways to excel combine columns into one list.

📁 Download Excel File

Learn to Combine Columns into One List in Excel with These 4 Approaches

Here we are going to use 4 different approaches to combine columns into one list in Excel. For that here is the dataset. In the dataset, we can see we have two columns of Names and we want to combine them into a list.

In this method, we will use INDEX, IFERROR, and ROWS function to excel combine columns into one list. Here we will combine the two columns of Names into one list.

⬇️⬇️ STEPS ⬇️⬇️

• At first, go to the Approach 1 worksheet.

• Select cell D6.

• Enter following formula.

• Press Enter key.

• Now select cell D6 and drag Fill Handle from cell D6 to D15.

Here we can see that we have combined the columns of Names into one single list under the Merged List of Names.

Here in the second approach, we are going to use a formula using ROW, COUNTA, INDEX, and IF functions.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, go to Approach 2

• Then select cell D6.

• Enter following formula.

• Then click Enter key.

• Then select cell D6 and drag the icon of Fill Handle from D6 to D15.

In the third method, we will use a combination of INDEX, ROW, COLUMNS, and MOD functions to combine columns into one list.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, go to Approach 3 worksheet.

• Secondly, select cell D6.

• Then enter following formula.

• Click Enter key.

• Now select cell D6 and drag the icon of Fill Handle icon from D6 to D15.

Here we also got the same result as the previous two methods. We can see the combined list under the merged list of names.

Finally, we will use a VBA code to combine columns into one single list.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, go to the Approach 4 worksheet.

• After that, press right-click on the worksheet name. Then you will get the following window and click the View code.

• After clicking on the View code you will get a window like below.

• Enter the code given below then click the Run icon.

Sub CombineColumns()
Dim xRange As Range
Dim j As Integer
Dim EndRow As Integer
Dim Txt As String
On Error Resume Next
Txt = Application.ActiveWindow.RangeSelection.Address
Set xRange = Application.InputBox("Select the required data range", "Merged List", Txt, , , , , 8)
If xRange Is Nothing Then Exit Sub
EndRow = xRange.Columns(1).Rows.Count + 1
For j = 2 To xRange.Columns.Count
Range(xRange.Cells(1, j), xRange.Cells(xRange.Columns(j).Rows.Count, j)).Cut
ActiveSheet.Paste Destination:=xRange.Cells(EndRow, 1)
EndRow = EndRow + xRange.Columns(j).Rows.Count
Next
End Sub

• After clicking the Run icon it gives you the following window.

• Now go to the worksheet and pick data range and click OK.

• And then you will see the names of column B merged with the names of Column A.

📝 Takeaways from This Article

📌  In this article we illustrated four different ways to excel combine columns into one list.

📌  Here, we have used different functions, such as: INDEX, COUNTA, ROW, IFERROR functions.

📌  We also Used a VBA code and learned the approach of using a VBA code.

Conclusion

Finally, we have come to the end of this article. In this article, we tried to provide every possible way to excel combine columns into one single list. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you want to share more queries, please feel free to share recommnedations in comment section. Finally, we propose that you visit Excelden to discover more about Excel.