How to Combine Rows into One Cell in Excel

Combine Rows in Excel Using Macro

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.

Data Set to Combine Rows into One Cell in Excel

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.

Merge & Center Tool in Excel Toolbar

  • Click on the Drop-Down menu. You will find a few options. Select Merge & Center.

Merge & Center Option in Excel

  • There will appear a prompt box telling you that only the value from the upper left will remain. Click OK.

Prompt Box to Merge & Center in Excel

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

Merged Rows Using the Merge and Center Tool in Excel

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:

=TEXTJOIN(delimiter,ignore_blank,text1,[text2],...)

The formula to combine all the books of Charles Dickens into one cell will be:

=TEXTJOIN(", ",TRUE,C4:C7)

TEXTJOIN Function to Combine Rows in Excel

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

=TEXTJOIN(", ",TRUE,C8:C10) 

TEXTJOIN Function to Combine Rows in Excel

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.

Data Set to Combine Rows in Excel

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:

=TEXTJOIN(", ",TRUE,C4:C7)

TEXTJOIN Function to Combine Rows in Excel

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:

=TEXTJOIN(", ",TRUE,C8:C11)

And the same for all the employees.

TEXTJOIN Function to Combine Rows in Excel

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:

Data Set to Combine Rows in Excel

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:

=UNIQUE(B4:B27)

UNIQUE Function to Combine Rows in Excel

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:

=TEXTJOIN(", ",TRUE,FILTER(C4:C27,B4:B27=E4))

TEXTJOIN Function to Combine Rows in Excel

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

Dragging the Fill Handle in Excel.


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:

=CONCATENATE(TRANSPOSE(C4:C7))

CONCATENATE TRANSPOSE Function to Combine Rows in Excel

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:

Data Set to Combine Rows into One Cell in Excel

For this type of data set, press Alt + F11 and go to the VBA window. Then insert this code in a new module:

Code:

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

VBA Code to Combine Rows in Excel

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.

Selecting a Data Set in Excel

Then press Alt + F8 on your keyboard. The Macro dialogue box will open. Select the Macro Merging_Rows and click on Run.

Macro Box in Excel

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.

Prompt Box While Merging Rows

You will find the rows in your data set have been combined beautifully like this.

Rows Combined Using Macro in Excel


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:

Data Set to Combine Rows in Excel

Open the VBA window by pressing Alt + F11 and enter this code in a new module:

Code:

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

VBA Code to Combine Rows in Excel

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.

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.

Sort Dialogue Box in Excel

Then click on OK. Your data set will be sorted according to the ascending or descending order of the Authors (The leftmost column).

Data Set Sorted in Excel

Now select this data set (Without the Column Headers) and run the Macro Merging_Rows2 in the same way as described above.

Running a Macro to Combine Rows in Excel

You will find similar rows of the data set combined into one cell like this.

Combine Rows in Excel Using Macro


Conclusion

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo