We often face some datasets, that have some repetitive values. In that case, it is very convenient to merge those rows and shows them in a single cell. In this article, I am going to explain how to merge rows with the same value in Excel. I used 5 quick ways to make it easy for you.
Download Practice Workbook
Get this practice file and try the methods by yourself.
5 Quick Ways to Merge Rows with Same Value in Excel
By using a datasheet, I am going to explain how to merge rows in Excel without losing data. The datasheet contains 3 columns. The columns are Customer Name, Product Name and Product Price.
As you can see, some Customer Names are given twice. For this reason, I will merge the rows and show them in a single cell.
1. Merge Rows with Same Value from Excel Ribbon
You can merge rows using the Merge & Center option from Ribbon in your Excel workbook. For this, go through the process below.
- First, select the rows with the same value you want to merge.
- Then, open the Home tab and expand Merge & Center option.
- Afterward, select Merge & Center.
- After this, a dialogue box will pop up with a message. It says it keeps the upper-left value.
- Here, click on OK.
- As a result, you will get the merged rows where the value is in the center.
- Otherwise, you can also merge rows specifying cells.
- For this, select the rows you want to merge.
- Then, open the Home tab and expand Merge & Center option.
- Here, select Merge Cells from the drop-down menu.
- Finally, it will merge the rows with the same value.
Read more: How to Merge Rows and Columns in Excel
2. Apply Consolidate Command to Merge Rows in Excel
To merge multiple rows with the same values you can use Consolidate command in Excel. It allows doing some operations using functions within the same values.
- First, select a cell where you want to keep merged rows with the same value.
- For instance, I separated the Customer Name and the Product Price columns and selected a cell from it.
- Then, open the Data tab and select Consolidate under the Data Tools category.
- Afterward, a dialogue box will pop up from where you can select the functions given. I selected the SUM function.
- Next, take the Reference of the Cell range B5:D14 as we want to consolidate them.
- Following this, Add this in All references.
- Lastly, mark the tick on the Left column as I am merging the Customer Name values and summing Product Price.
- Finally, click on OK.
- As a result, you will get the merged same values with the SUM of Product Price.
Read more: Merge Duplicate Rows in Excel
3. Use IF Function to Merge Rows with Same Value
The IF function is also very helpful for merging rows with the same values. Let’s see how it works.
- First, select the Cell range B5:B14 as we want to sort the Customer Names alphabetically.
- Then, go to the Data tab and click on Sort & Filter.
- Here, choose Sort A to Z.
- After this, create a new column titled Merged Rows with Same Values.
- Here, select Cell E5 and insert this formula.
=IF(B5=B4,E4&", "&C5,C5)
- Afterward, it will start comparing consecutive values. Here the first value didn’t find the same value.
- Now, select the Cell E5 cell and double-click on the right corner to copy the formula for the rest of the cells.
- Finally, it’s showing the merged values of the same rows.
4. Merge Multiple Rows with Same Value Using Excel VBA
Excel VBA is a quicker option to merge rows with same values. Especially, when the dataset is quite large. To do the task, go through these steps.
- First, sort the rows based on which you want to apply VBA.
- Here, I selected the Cell range B5:B14 as I want to sort the Customer Name column.
- Now, click on Sort from Data Tab.
- Then, the Sort Warning dialogue box will pop up.
- Here, choose the option Expand the selection and click on Sort.
- Afterward, select Customer Name as the Sort by option.
- Then, click on OK.
- As a result, it will give the sorted values of the Customer Name.
- Next, press Alt + F11 on your keyboard to open the Microsoft Visual Basics for Applications window.
- Here, choose Module from the Insert tab.
- Now, write this code on the Code window.
Option Explicit
Sub MergeSameCells()
Application.DisplayAlerts = False
Dim rg As Range
MergeCells:
For Each rg In Selection
If rg.Value = rg.Offset(1, 0).Value And rg.Value <> "" Then
Range(rg, rg.Offset(1, 0)).Merge
GoTo MergeCells
End If
Next
End Sub
- After this, press F5 on your keyboard to run the code.
- Following this, click on Run in the Macros window.
- Finally, you will get the merged rows all at once.
5. Create Helper Column to Merge Same Valued Rows
In this last method, I will insert a helper column to associate with the merging process. For this, follow the steps below.
- First, select the Cell range B5:B14.
- Then, select Data > Outline > Subtotal.
- Following this, you will get a warning message window.
- Here, click on OK.
- As a result, you will get the Subtotal dialogue box.
- In this box, keep the selections as shown below and click on OK.
- Afterward, you will get a helper column just beside the original dataset.
- In this column, select the Cell range B5:B20.
- Now, press Ctrl + G on your keyboard to open the Go To window.
- Here, click on Special.
- Then, select Blanks in the Go To Special window and click on OK.
- As a result, all the blank cells will be selected like this.
- Next, keeping this selection, select Home > Alignment > Merge & Center.
- Then, choose Format Painter from the Home tab.
- Accordingly, you will get this Brush icon.
- Now, hover it through the Customer Name column to match the format of the helper column.
- Following this, open the Subtotal window again.
- Here, choose Count in the Use function list.
- Also, choose Product Price under the Add subtotal to box.
- Lastly, press Remove All and then delete the helper column.
- Finally, you will get the merged rows with same values.
Conclusion
From this article, you will learn in 5 quick ways how to merge rows with the same value in Excel. I hope my explanation will help you to find your solution. Feel free to comment down below. Learn more about Excel with ExcelDemy.
Thank you so much for this, saved me a lot of hours of work!
You’re welcome, CHRIS! Best regards!
Very useful. thanks
Merging “visually” (i.e. keeping the value and the sorting/filtering features) same values in column is a feature that has been missing in Excel for many years! About 20 years ago already, I had developped a VBA macro of the kind shown here to merge the cells, but I believe MS should include such feature in the core Excel…