How to Merge Rows with Same Value in Excel (5 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

We often face some datasets, that have some repetitive values. In that case, it is very convenient to merge those rows and show 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.


How to Merge Rows with Same Value in Excel: 5 Quick Ways

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.

Excel Merge Rows with Same Value


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.

Merge Rows with Same Value from Excel Ribbon

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

Excel Merge Rows with Same Value

  • Finally, it will merge the rows with the same value.


2. Apply Consolidate Command to Merge Rows in Excel

To merge multiple rows with the same values you can use the 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.

Apply Consolidate Command to Merge Rows in Excel

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

Apply Consolidate Command to Merge Rows in Excel

  • Finally, click on OK.
  • As a result, you will get the merged same values with the SUM of Product Price.


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.

Use IF Function to Merge Rows with Same Value

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

Use IF Function to Merge Rows with Same Value

Here, the IF function states the condition B5=B4 and if it is true it will return the values of C5 inside E4. The separator is Comma and so we provided it as &”, “&.
  • 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 shows 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.

Merge Multiple Rows with Same Value Using Excel VBA

  • 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

Merge Multiple Rows with Same Value Using Excel VBA

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

Read More: How to Combine Rows with Same ID in Excel


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.

Create Helper Column to Merge Same Valued Rows

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

Create Helper Column to Merge Same Valued Rows

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

Create Helper Column to Merge Same Valued Rows

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

Create Helper Column to Merge Same Valued Rows

  • Finally, you will get the merged rows with same values.

Read More: How to Merge Rows in Excel Based on Criteria


Download Practice Workbook

Get this practice file and try the methods by yourself.


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.


Further Readings


<< Go Back to Merge Rows in Excel  | Merge in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

4 Comments
  1. Thank you so much for this, saved me a lot of hours of work!

  2. Very useful. thanks

  3. 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…

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo