Md. Sourov Hossain Mithun
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.
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.
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.
👇 Formula Breakdown
➥ 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 passes the calculation to the next portion when the first portion starts to return errors. It is also right for the second portion, when errors return the calculation continues with the third portion. 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.
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.
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.
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.
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.
Things to Remember
- The VBA process is irreversible. So keep a back up beforehand.
- The cell references in the formulas used need to be very precise. Otherwise the formulas may not work or return unexpected values.
- Every demonstration shown in those picture was made with Excel 365. So, results may vary for other versions.
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.
In method 2, I would like the cell reference in the last row funtion to autocalculate based on the length of the first column.
So in ➥ ROW()-ROW($D$8)
I would like the “8” to be calculated (essentially using the counta value from earlier)
Possible? I have varying length pivot tables that need combining. Thanks.
Hello DANIEL,
Yes, it’s possible to do that using the COUNTA function based on the first column. For that, use the following formula-
=IF(B5<>"",INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8)),INDEX($C$5:$C$1048576,ROW()-COUNTA($B$5:$B$8)-4))➥
ROW()-COUNTA($B$5:$B$8)-4Here, 4 is subtracted based on the length of the first column to return 1 as the output of this portion. So for your own dataset, modify the value according to the length of your first column.
Hello, how would method 2 look like if i want to combine 3 columns.
Hello EYAD,
Thanks for your feedback. It’s possible to combine 3 columns using the 2nd method after a little bit modification of the formula.
I added more 4 products in column D and then applied this formula in Cell E5:
=IF(B5<>"",INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8)),IF(C1<>"",INDEX($C$5:$C$1048576,ROW()-ROW($E$8)),INDEX($D$5:$D$1048576,ROW()-11)))*INDEX($D$5:$D$1048576,ROW()-11)
Here, 11 is used based on the length of the second column.
Hi, thank you for this article. Can you modify #3 so I can have the merged-list to show all the column 1 one before starting listing on column 2 then column 3 and so on. At this time #3 does not behave like #1 and #2. Thank you in advance!
Hello MICHAEL,
Thanks for your inquiry. Actually, the formula used in Method-1 has been updated to use in Method-3 for behaving differently from Method-1. In Method 1 we tried to combine two columns by adding another column after the ending of one column. But in Method-3 it was the intent to join 2 columns by adding the cell contents row-wise. If you need to add the columns serially then please follow the previous 2 methods.
Thank you
Tanjima Hossain
Thank you for the VBA code. Is it possible to modify the VBA code so the merged-list does not have an empty cells because the row length of the original columns vary. Say column 1 has 20 rows, column 2 has 1 and column 3 has 100 (and I have 50 columns); because the input from the data range will have to say $A$1:$BZ$100, then value of x.Columns(i).Rows.Count is always 100 and the resulting merged-list has a LOT of empty cells. Thank you in advance!
Hello Michael,
Hope you are doing well. Here, I have the following dataset containing 3 columns where we have some products. After combining all of the columns into a column we will clean up all of the empty cells.
• Write down the following code in a module.
• Press F5.
Then, you will get the following input box.
• Select the range of cells containing texts and press OK.
Later, another input box will appear.
• Select the whole data range containing all the blank cells.
• Press OK.
In this way, we combined all of the columns in the first column and deleted the rest of the cells.
Best Regards
Tanjima Hossain