Today I will be showing how to combine rows into one cell in Excel.
While working in Excel, trying to combine rows or columns is one of the most common tasks that we perform. Today I will be showing how you can combine rows into one cell in Excel.
Download Practice Workbook
How to Combine Rows into One Cell in Excel
Here we’ve got a data set with the names of some Authors and their Books available in a bookshop called Kingfisher Bookstore.
Our objective today is to combine all the books of each writer into one cell.
1. Combining Rows Using Merge & Center Tool from Excel Toolbar (Losing Data)
We can try to use the Merge & Center tool from Excel Toolbar. To use that:
- Select the cells that you want to merge and go to the Home>Merge & Center tool in Excel Toolbar.
Here I have selected the books of the first author, Charles Dickens.
- Click on the Drop-Down menu. You will find a few options. Select Merge & Center.
- There will appear a prompt box telling you that only the value from the upper left will remain. Click OK.
- You will find the rows have merged, but they contain only the value from the first cell. A Tale of Two Cities in this example.
This is not exactly what we want. Therefore, the Merge & Center tool doesn’t come much helpful here.
We have to search for alternative ways.
2. Combining Rows Using Formula (Without Losing Data)
Option 1: Using the TEXTJOIN Function
We can combine the values from multiple rows into one single row using the TEXTJOIN function of Excel.
Note: The TEXTJOIN function is available only in Office 365 and Excel 2019.
The Syntax of the TEXTJOIN function is:
The formula to combine all the books of Charles Dickens into one cell will be:
- Here “, “ is the delimiter by which we wanted to separate the names of the books. You can use anything you wish as a delimiter.
- The ignore_blank argument has been set to be TRUE. If there had been any blank cell within our given range, that would have been ignored.
- C4:C7 is the range of the books of Charles Dickens that we want to combine. You use what you need.
Now you have to enter this formula for the rest of the authors manually.
For example, for P. B. Shelly, the formula will be:
This method does work but is not quite practical when we have a very large data set in our hands.
Because entering a large number of formulas separately can be quite troublesome.
But this process will come in handy when all the subsets have the same number of rows.
For example, look at this data set. We have the Names of some employees and their scheduled Workdays.
Here, all the employees have the same number of workdays, that is 4 days.
Here you can enter the TEXTJOIN formula for the first employee:
Then copy the formula using Ctrl + C and paste it alongside the rest of the employees using Ctrl + V.
As we have used the relative cell reference, Excel will automatically adjust it while copying and pasting.
For example, if you paste the formula in D8 alongside Natalia Austin, Excel will convert it to:
And the same for all the employees.
Before going to the next section, I want to add one more thing.
If your data set doesn’t have the different classes separated specifically, rather they are mixed randomly, like this data set:
You can use a slightly different process to combine similar rows.
First, use this formula to extract the Names of all the different Authors in a new column:
Here, B4:B27 is the list of my Authors. You use it according to your needs.
Then insert this formula beside the name of the first Author:
- Here C4:C27 is the list of all my Books.
- B4:B27 is the list of my Authors.
- And E4 is the first cell of the Authors.
After inserting the formula in the first cell, drag the Fill Handle to copy the formula to the rest of the cells.
Option 2: Using the CONCATENATE and TRANSPOSE Function
The TEXTJOIN function is only available in Office 365 and Excel 2019.
So those who do not have a subscription can follow this procedure.
We will use a combination of the CONCATENATE and the TRANSPOSE function here.
For combining the books of the first Author, use this formula:
Note: Here C4:C17 is the range of the Books of my first Author, Charles Dickens. You use it according to your needs.
Then repeat the formula manually for all the other Authors.
Like the previous section, if all your subsets have the same number of rows, then you can just copy and paste it into respective cells.
And if they are not, you have to enter the formulas manually.
3. Combining Rows Using a VBA Macro (Without Losing Data)
The two methods described above are helpful, yet seem to not fulfill our expectations to the fullest.
Now we will develop a Macro using VBA code that will fulfill all our expectations.
We will develop two Macros for two different cases.
Click here to see how to save and run Macros in Excel.
Case 1: Different Classes Separated Specifically
First of all, we consider the case where different classes of data are separated specifically, like this:
For this type of data set, press Alt + F11 and go to the VBA window. Then insert this code in a new module:
Sub Merging_Rows() Dim out As Variant out = "" Dim start As Variant start = 1 Dim ending As Variant ending = 1 Dim i As Variant Dim j As Variant For i = 2 To Selection.Rows.Count + 1 If Selection(i, 1) <> "" Or i = Selection.Rows.Count + 1 Then ending = i - 1 For j = start To ending If j = ending Then out = out + Range(Selection(j, 2).Address).Value Else: out = out + Range(Selection(j, 2).Address).Value + vbNewLine End If Next j Range(Selection(start, 2).Address) = out Range(Selection(start, 1).Address + ":" + Selection(ending, 1).Address).Merge Across:=False Range(Selection(start, 2).Address + ":" + Selection(ending, 2).Address).Merge Across:=False start = i out = "" End If Next i End Sub
Save the file as Excel Macro-Enabled Workbook.
Then come back to your worksheet and select the whole data set (Without the Column Headers) that you want to combine.
Then press Alt + F8 on your keyboard. The Macro dialogue box will open. Select the Macro Merging_Rows and click on Run.
You will get a few prompt boxes telling you that only the value from the upper left cell will remain. Ignore those. Click OK each time.
You will find the rows in your data set have been combined beautifully like this.
Case 2: Different Classed Mixed Randomly
Now consider in your data set, different classes are not separated specifically, rather they are mixed randomly, like this:
Open the VBA window by pressing Alt + F11 and enter this code in a new module:
Sub Merging_Rows2() Dim out As Variant out = "" Dim start As Variant start = 1 Dim ending As Variant ending = 1 Dim i As Variant Dim j As Variant For i = 1 To Selection.Rows.Count If Selection(i, 1) <> Selection(i + 1, 1) Then ending = i For j = start To ending out = out + vbNewLine + Range(Selection(j, 2).Address).Value Next j Range(Selection(start, 2).Address) = out Range(Selection(start, 1).Address + ":" + Selection(ending, 1).Address).Merge Across:=False Range(Selection(start, 2).Address + ":" + Selection(ending, 2).Address).Merge Across:=False start = i + 1 out = "" End If Next i End Sub
Save the file as Excel Macro-Enabled Workbook.
Then come back to your worksheet. Select the whole data set (Including the Column Headers) and go to the Home>Sort & Filter tool in Excel Toolbar.
Select Custom Sort from the Sort & Filter drop-down menu. The Sort dialogue box will open.
Under the Column option from the Sort by menu, select Author (The first column of your data set)
Then under the Sort On option, select Cell Values.
Under the Order option, select A to Z or Z to A.
Then click on OK. Your data set will be sorted according to the ascending or descending order of the Authors (The leftmost column).
Now select this data set (Without the Column Headers) and run the Macro Merging_Rows2 in the same way as described above.
You will find similar rows of the data set combined into one cell like this.
Using these methods, you can combine rows in Excel to differentiate similar types of data. Do you have any other questions? Feel free to ask us.