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.

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

- 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)),””))**

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

🔨 **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**.

- 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)))**

- Then click
**Enter**key.

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

🔨 **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**.

- 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)**

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

🔨 **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.

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