Excel Merge Rows with Same Value (4 Ways)

In this article, I am going to explain how to merge rows with the same value. I used multiple ways to make it easy for you.

By using a datasheet, I am going to explain how to merge rows in Excel without losing data. The datasheet contains two columns. The columns are First Name, Product Name and Product Price.

Sample Datasheet of Merging Rows

Sample Datasheet to Practice

Ways to Merge Rows with Same Value in Excel

1. Using Ribbon

I. Using Merge & Centre

You can merge rows using the Merge & Centre option from Ribbon. First, select the rows with the same value you want to merge. Then open the Home tab then expand Merge & Centre option and select Merge & Centre.

Multiple rows Merge & Centre

After selecting Merge & Centre a dialogue box will pop up with a message. Where it says it keeps the upper-left value. Now click on OK.

Merge & Centre

After clicking OK you will get the merged rows where the value is in the Centre.

Merged values of multiple rows with same value

Read more: How to Merge Rows and Columns in Excel

    II. Merging Rows using Merge Cells

First, select the rows you want to merge then open the Home tab and expand Merge & Centre. From these options select Merge Cells.

Merge cells to merged multiple rows

After selecting Merge Cells it will pop up a message which values it is going to keep. Then click on OK.

Merge Cells

After clicking on OK It will merge the rows with the same value.

Merged values of multiple rows with same value

2. Using Consolidate Command

To merge multiple rows with the same values you can use Consolidate. Consolidate also allows doing some operations using functions within the same values.

Before applying Consolidate to put the merged rows select the cells where you want to keep merged rows with the same value. I selected two columns.

First, open the Data tab then selects Consolidate.

Merged multiple same valued rows using consolidate

After selecting Consolidate a dialogue box will pop up from where you can select the functions given. I selected the SUM function.

Merged multiple same valued rows using consolidate

Then take the Reference of the multiple cells you want to consolidate. Add this in All Reference. Mark on the Left column as I am Merging the First name values and summing Product Price. Then click on Ok.

Merged multiple same valued rows using consolidate

Here are the Merged same values with the SUM of Product Price.

Merged multiple rows with same value

Read more: Merge Duplicate Rows in Excel

3. Merging Rows Using Formula

Using the IF function, you can merge the same values in Excel.

First, go to the Data tab then click on Sort to sort the table based on which you want to merge rows with the same values. After sorting select the cell where you want to keep the same values then press (=) type the IF function.

I sorted the First Name column using Sort A to Z.

Merged multiple same valued rows using formula

The formula is =IF(B4=B3,E3&", "&C4,C4)

Merging multiple rows with same value using formula

After typing the formula in Formula Bar press Enter. It will start comparing consecutive values. Here the first value didn’t find the same value.

Using Formula

Now select the E4 cell and double click on the right corner to copy the formula for the rest of the cells. Now it’s showing the merged values of the same rows.

Using Formula

4. Merging Multiple Rows with Same Values Using VBA

First, sort the rows based on which you want to apply VBA. I sorted the First Name column.

Sorting column

From Data Tab click on Sort then a dialogue box will pop up. Click on Sort.

Sorting dialog box

Select Sort by option. I selected First Name.  Then click on OK.

Sorting by first name column

Now it will give the sorted values of First Name.

Sorted Values

You can open VBA from developer tools, or you also can press ALT+F11.

Pressing ALT+F11 opened the VBA Editor.

VBA Editor

Wrote the code for merging multiple rows with the same values in the editor.

Option Explicit

Sub MergeSameCells()

Application.DisplayAlerts = False

Dim rng As Range

MergeCells:

For Each rng In Selection

If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then

Range(rng, rng.Offset(1, 0)).Merge

GoTo MergeCells

End If

Next

End Sub

You can compile the code from Menu Bar or using F8. After selecting the range, it will run the code line by line. You can see that the first multiple rows with the same value are merged.

Now by pressing run from the Menu Bar or pressing F5 you can execute the whole code for the selected range.

Merged rows with the same value.

Conclusion

From this article, you will find several methods of merging multiple rows with the same value. I hope my explanation will help you to find your solution. You are most welcome to give any kind of suggestions, ideas, feedback. Feel free to comment down below.


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.

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

Leave a reply

ExcelDemy
Logo