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.

combine columns into one list dataset


Approach

1. Applying INDEX, ROWS and IFERROR Functions

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.

Using INFERROR, INDEX and ROWS function to Combine Columns into One List

  • Select cell D6.

  • Enter following formula.
=IFERROR(INDEX($B$6:$B$11, ROWS(D6:$D$6)), IFERROR(INDEX($C$6:$C$9, ROWS(D6:$D$6)-ROWS($B$6:$B$11)),””))

Utilizing INFERROR, INDEX and ROWS function to Combine Columns into One List

  • Press Enter key.

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

Applying INFERROR,INDEX and ROWS function to Combine Columns into One List

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

🔨 Formula Breakdown

IFERROR(INDEX($B$6:$B$11, ROWS(D6:$D$6)), IFERROR(INDEX($C$6:$C$9, ROWS(D6:$D$6)-ROWS($B$6:$B$11)),””)) 

👉  Here ROWS($B$6:$B$11) this term returns number of available rows. Which is 6 in this case.

👉  ROWS(D6:$D$6) this expands while formula goes to the cells below. It always returns 1.

👉  ROWS(D6:$D$6)-ROWS($B$6:$B$11) this returns -5.

👉  Here the INDEX($C$6:$C$9, ROWS(D6:$D$6)-ROWS($B$6:$B$11)) returns error value. Because of negative number -5.

👉  IFERROR(INDEX($C$6:$C$9, ROWS(D6:$D$6)-ROWS($B$6:$B$11)),””) here IFERROR function finds for errors if it gets any it returns a empty string.

👉  INDEX($B$6:$B$11, ROWS(D6:$D$6) here it also returns value from array row 1. Which is Adam.

👉  IFERROR(INDEX($B$6:$B$11, ROWS(D6:$D$6)), IFERROR(INDEX($C$6:$C$9, ROWS(D6:$D$6)-ROWS($B$6:$B$11)),””))  here IFERROR function looks for errors and if it finds one it passes calculation to next portion. And returns Adam finally.


Approach

2. Utilizing COUNTA, INDEX and ROW Functions

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.

Utilizing IF, ROW, COUNTA and INDEX Functions to Combine Columns into One list in Excel

  • Enter following formula.
=IF(B6<>””,INDEX($B$6:$B$1048576,ROW()-COUNTA($B$6:$B$10)),INDEX($C$6:$C$1048576,ROW()-ROW($D$10)))

Using IF, ROW, COUNTA and INDEX Functions

  • Then click Enter key.

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

Applying IF, ROW, COUNTA and INDEX Functions

🔨 Formula Breakdown

IF(B6<>””,INDEX($B$6:$B$1048576,ROW()-COUNTA($B$6:$B$10)),INDEX($C$6:$C$1048576,ROW()-ROW($D$10)))

👉  ROW()-ROW($D$10) here ROW function provides cell number D6 and ROW($D$10) gives cell number D10 and after subtraction returns -4.

👉  INDEX($C$6:$C$1048576),ROW()-ROW($D$10))) here INDEX function also gives values from array $C$6:$C$1048576 and after the subtraction, it also gives a negative value and returns #Value!.

👉  ROW()-COUNTA($B$6:$B$10) here the COUNTA function counts non-blank cells and subtracts it from cell number D6 and gives 1.

👉  INDEX($B$6:$B$1048576,ROW()-COUNTA($B$6:$B$10) it takes the value of the array form B6 to B1048576 and the row number of 1 and returns Adam.

👉  IF(B6<>””,INDEX($B$6:$B$1048576,ROW()-COUNTA($B$6:$B$10)),INDEX($C$6:$C$1048576,ROW()-ROW($D$10))) here the IF evaluates whether or not B6 is empty.If empty it follows first INDEX function and otherwise it returns the second’s INDEX function.


Approach

3. Using INDEX, INT and MOD Functions

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.

Using INDEX, INT, COLUMNS, MOD and ROW Functions

  • Then enter following formula.
=INDEX($B$6:$C$10,1+INT((ROW(B6)-6)/COLUMNS($B$6:$C$10)),MOD(ROW(B6)-6+COLUMNS($B$6:$C$10),,COLUMNS($B$6:$C$10))+1) 

Applying INDEX, INT, COLUMNS, MOD and ROW Functions to Excel Combine Columns into One List

  • Click Enter key.

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

Utilizing INDEX, INT, COLUMNS, MOD and ROW Functions to Excel Combine Columns into One List

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

🔨 Formula Breakdown

INDEX($B$6:$C$10,1+INT((ROW(B6)-6)/COLUMNS($B$6:$C$10)),MOD(ROW(B6)-6+COLUMNS($B$6:$C$10),COLUMNS($B$6:$C$10))+1)

👉  COLUMNS($B$6:$C$10) it returns the columns of the array. Which is 2.

👉  ROW(B6) this function returns the B6 row number and which is 6.

👉  MOD(ROW(B6)-6+COLUMNS($B$6:$C$10),COLUMNS($B$6:$C$10)) here The remaining calculation is returned by the MOD function. Here it will provide 0.

👉  INT((ROW(B6)-6)/COLUMNS($B$6:$C$10) here int function will provide integer value.

👉  INDEX($B$6:$C$10,1+INT((ROW(B6)-6)/COLUMNS($B$6:$C$10)),MOD(ROW(B6)-6+COLUMNS($B$6:$C$10),COLUMNS($B$6:$C$10))+1) and finally the whole function will provide the value from B6 to C10. and we will get Adam.


Approach

 4. Executing VBA Code

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

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Approach 4 worksheet.

Executing VBA Code to Excel Combine Columns into One List

  • 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.

(Visited 65 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo