How To Combine Two Blogs Into One

If you want to combine multiple columns into one list, normally, you can copy and paste the columns' data one by one into the specified column. But, if the dataset is long then it won't be feasible to do it. So here in this tutorial, I'll introduce you to some easy methods to combine columns into one list in Excel.

Download Practice Book

You can download the free Excel template from here and practice on your own.

4 Easy Ways to Combine Columns into One List in Excel

Method 1: Use IFERROR, INDEX, and ROWS Functions Together to Combine Columns into One List in Excel

Let's get introduced to our dataset first. Here, I have placed some cloth items in two columns. Our goal is to bring them in one column that means combining columns into one list. In our very first method, I'll use the IFERROR, INDEX , and ROWS functions to do the operation. The IFERROR function is applied to return a result or message when an error is found or to return the output of the expression. The INDEX function returns a value or the reference to a value from within a data range. The ROWS function returns the number of rows in a reference or array.

IFERROR, INDEX, and ROWS Functions Together to Combine Columns into One List in Excel

Step 1:

➤ Write the following formula in Cell D5:

=IFERROR(INDEX($B$5:$B$8, ROWS(D5:$D$5)), IFERROR(INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8)),""))

➤ Then just hit the Enter button.

IFERROR, INDEX, and ROWS Functions Together to Combine Columns into One List in Excel

Step 2:

➤ After that, drag the Fill Handle icon over the cells under Cell D5 until the formula returns the last data, "Backpack".

Here it is! We have successfully combined the products in two columns into a single column.

👇 How Does the Formula Work:

➥ ROWS($B$5:$B$8)
The ROWS function returns the number of rows in the range B5:B8:
{4}

➥ ROWS(D5:$D$5)
D5:$D$5 is special, it expands as the formula is copied to cells below. In Cell D5 it returns:
{1}

➥ ROWS(D5:$D$5)-ROWS($B$5:$B$8)
After subtracting, the result is:
{-3}

➥ INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8))
Then the INDEX function returns the value from the array (C5:C7) as specified by the row number {-3}. As it's a negative number, so the output shows an error:
{#VALUE!}

➥ IFERROR(INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8)),"")
The IFERROR function checks for an error from the output of the INDEX function. If there is any, then it returns an empty string. In this case, it returns:
{}

➥ INDEX($B$5:$B$8, ROWS(D5:$D$5)
Here also the INDEX function returns the value from the array for row number 1:
{Hat}

➥ IFERROR(INDEX($B$5:$B$8, ROWS(D5:$D$5)), IFERROR(INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8)),""))
Finally, The IFERROR function moves the calculation to the next part when the first part begins to return errors. That is also true for the second part, when errors occur the calculation continues with the third part. So finally it returns:
{Hat}

Method 2: Combine IF, ROW, COUNTA, INDEX Functions to Join Columns into One List in Excel

In this method, we'll use another combination of functions, which are: the IF, ROW, COUNTA, and INDEX functions. The IF function returns one value if a condition is true and another value if it's false. The ROW function will give the row number of any cell and the COUNTA function counts the number of non-blank cells.

Step 1:

➤ In Cell D5 type the given formula-

=IF(B5<>"",INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8)),INDEX($C$5:$C$1048576,ROW()-ROW($D$8)))

➤ Now press the Enter button.

IF, ROW, COUNTA, INDEX Functions to Join Columns into One List in Excel

Step 2:

➤ Then drag the Fill Handle icon to copy the formula.

👇 Formula Breakdown:

➥ ROW()-ROW($D$8)
The ROW function returns the row number of Cell D5. Then the row number of Cell D8 will be subtracted from it. The output is:
{-3}

➥ INDEX($C$5:$C$1048576,ROW()-ROW($D$8))
Then the INDEX function returns the value from the array (C5:C1048576) according to the row number specified by the previous piece of the formula. As it is negative so it returns:
{#VALUE!}

➥ ROW()-COUNTA($B$5:$B$8)
It returns the sum after subtracting the non-blank cell numbers of the array (B5:B8) from the row number of Cell D5 and returns:
{1}

➥ INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8))
Now the INDEX function returns the value from the array (B5:B1048576) according to that number of position. That is:
"Hat"

➥ IF(B5<>"",INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8)),INDEX($C$5:$C$1048576,ROW()-ROW($D$8)))
The IF function will check Cell B5 whether it is empty or not. If so, then it will show the result according to the first INDEX operation, and if not then it will show the result of the second INDEX operation. Finally, it will return as-
"Hat"

Method 3: Apply INDEX, INT, COLUMNS, MOD, and ROW Functions to Merge Columns into One List in Excel

We can use the INDEX, INT, COLUMNS, MOD, and ROW functions together to combine columns into one list. The INT function returns the numeric integer equivalent from a given expression. The COLUMNS function returns the number of columns in a reference or array. The MOD function returns the remainder of two numbers after division.

Step 1:

➤ Type the formula in Cell D5–

=INDEX($B$5:$C$7,1+INT((ROW(B5)-5)/COLUMNS($B$5:$C$7)),MOD(ROW(B5)-5+COLUMNS($B$5:$C$7),COLUMNS($B$5:$C$7))+1)

➤ Then click the Enter button to get the result.

INDEX, INT, COLUMNS, MOD, And ROW Functions to Merge Columns into One List in Excel

Step 2:

➤ To copy the formula for the other cells, just use the AutoFill option.

👇 Formula Breakdown:

➥ COLUMNS($B$5:$C$7)
It will give the number of columns from the array (B5:C7) that will return as-
{2}

➥ ROW(B5)
The ROW function will return the row number of Cell B5. That is-
{5}

➥ MOD(ROW(B5)-5+COLUMNS($B$5:$C$7),COLUMNS($B$5:$C$7))
The MOD function will give the remainder after the calculation of ROW and COLUMNS functions and it will return as-
{0}

➥ INT((ROW(B5)-5)/COLUMNS($B$5:$C$7))
The INT function will then return the integer value by rounding it after the calculation. That will return as-
{0}

➥ INDEX($B$5:$C$7,1+INT((ROW(B5)-5)/COLUMNS($B$5:$C$7)),MOD(ROW(B5)-5+COLUMNS($B$5:$C$7),COLUMNS($B$5:$C$7))+1)
Finally, the INDEX function will return values from the range (B5:C7) based on the calculated row number and column number that will return as-
"Hat"

Method 4: Embed Excel VBA to Combine Columns into One List in Excel

If you like to code then it is possible to combine columns into one list by using VBA Macros. Here, we'll combine Columns B and C into Column B.

Step 1:

➤ Right-click on your sheet title then select View Code from the context menu.

A VBA window will appear.

VBA to Combine Columns into One List in Excel

Step 2:

➤ Later, type the given codes.

          Sub CombineColumns1() Dim x As Range Dim i As Integer Dim LastRow As Integer Dim zTxt As String On Error Resume Next zTxt = Application.ActiveWindow.RangeSelection.Address Set x = Application.InputBox("please select the data range", "Merged List", zTxt, , , , , 8) If x Is Nothing Then Exit Sub LastRow = x.Columns(1).Rows.Count + 1 For i = 2 To x.Columns.Count Range(x.Cells(1, i), x.Cells(x.Columns(i).Rows.Count, i)).Cut ActiveSheet.Paste Destination:=x.Cells(LastRow, 1) LastRow = LastRow + x.Columns(i).Rows.Count Next End Sub

➤ Now press the Play icon to run the codes.

VBA to Combine Columns into One List in Excel

A dialog box named "Merged List" will open up to select the data range.

Step 3:

➤ Then select the data range

➤ Finally, press OK

Now take a look at the image below that the products have listed in one column.

Conclusion

I hope all of the methods described above will be good enough to combine columns into one list. Feel free to ask any questions in the comment section and please give me feedback.