Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

Excel Merge Rows with Same Value


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.

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.

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.

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.

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.

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

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.


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.


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.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

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

ExcelDemy
Logo