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

How to Merge Duplicate Rows in Excel (5 Effective Ways)

While dealing with Microsoft Excel at home and office, at super shops or corporate companies, we often need to merge duplicate rows in Excel. There are different effective and comfortable techniques in Excel to assimilate duplicate worksheet rows and add up the results. Today we will show five of them with suitable examples and proper illustrations.


Download Practice Workbook


5 Ways to Merge Duplicate Rows in Excel

Let’s assume, we have a data set containing sales data of several sales representatives in an Excel worksheet. We need to merge the data set so that each sales representative is recorded only once in the table together with his total sales. We will show 5 widely used methods for merging duplicate rows to consolidate our sample data.

merge duplicate rows in excel


Method-1: Using Consolidate Option to Merge Duplicate Rows in Excel

The Consolidate option is used to combine information from multiple rows, worksheets, or workbooks into one place. It helps you to summarize your information from your data table from its different locations. We will see step by step how this tool helps us in solving our problems. To gather the consolidated data we created a table on the right side.

Using Consolidate Option to Merge Duplicate Rows in Excel

Steps:

  • Select the first cell of the range where you want the output.
  • Go to the Data tab >> Data Tools group >> Consolidate.

consolidate

Afterward, the Consolidate wizard will open up.

  • From the Function drop-down, select Sum (or any option you find useful for your task), and choose the range in the Reference
  • Click on Add.

Then, our selected range will appear inside the All references box.

  • Check the Left column option and press OK.

range in reference

In the end, you will get the unique list of the sales representatives along with their total sales from your initial data set.

Using Consolidate Option to Merge Duplicate Rows in Excel

Read More: How to Merge Rows in Excel (4 Methods).


Method-2: Applying Subtotal Feature

Here, we will use the Subtotal feature to combine the sales values of the duplicate sales representatives.

Applying Subtotal feature to Merge Duplicate Rows in Excel

Steps:

First, we need to sort the names in order so that the duplicate names can stay together.

  • Select the range, and then go to the Home tab >> Sort & Filter dropdown >> Custom Sort.

Applying Subtotal feature to Merge Duplicate Rows in Excel

Later, the Sort dialog box will open up.

  • Select the following options.
    • Sort by → Sales Rep column
    • Sort On → Cell Values
    • Order → A to Z
  • Press OK.

sorting

Later, the sales values with their corresponding representatives have been arranged in the following order.

sorted data

  • Select the data range, then go to the Data tab >> Outline group >> Subtotal.

Applying Subtotal feature to Merge Duplicate Rows in Excel

Then, you will have the Subtotal dialog box.

  • Select the following column names and options in their assigned fields.
    • At each change in → Sales Rep
    • Use function → Sum
    • Add subtotal to → Sales
  • Click on the Replace current subtotals and Summary below data options and press OK.

subtotal

Later, the sales for each representative will be grouped and their sales values will be added up.

  • Click on the minus(-) sign beside Bill Total to minimize the two different sales values achieved by this person.

minimize

So, we can see that the duplicate rows of the person Bill has been merged here.

Applying Subtotal feature to Merge Duplicate Rows in Excel

Similarly, we have done this job for other sales representatives also.

You can add borders for the last two rows.

Applying Subtotal feature to Merge Duplicate Rows in Excel


Method-3: Inserting Pivot Table to Merge Duplicate Rows in Excel

A Pivot Table is a tremendously effective MS Excel tool to sum, consolidate, and inspect data in Excel. In this method, we will show how to utilize this tool to merge duplicate rows and serve our purposes.

Inserting Pivot Table to Merge Duplicate Rows in Excel

Steps:

  • Select the range of cells and then go to the Insert tab >> PivotTable.

The Create PivotTable dialog box will open up.
You can see that your selected range has appeared in the Table/Range field.

  • Click on New Worksheet and press OK.

PivotTable wizard

As a result, you will be taken to a new sheet where on the left side the PivotTable and on the right side the PivotTable Fields will appear.

Inserting Pivot Table to Merge Duplicate Rows in Excel

  • Drag down the Sales Rep to the Rows area and Sales to the Values area.

Then, the PivotTable will appear on the left side.

drag fields

Later, we changed the formatting of the cells and added borders.

If you want to disappear the total value, then go to the PivotTable Analyze tab >> PivotTable dropdown >> Options.

options

Afterward, the PivotTable Options wizard will pop up.

  • Go to the Totals & Filters tab and unclick the following indicated options for grand totals.
  • Press OK.

unclick grand total

Then, the following table with merged rows for duplicate persons will appear.

Inserting Pivot Table to Merge Duplicate Rows in Excel


Read More: How to Merge Rows in Excel Based on Criteria (Easiest Ways)

Similar Readings


Method-4: Applying IF Function to Merge Duplicate Rows in Excel

In this section, we will use the IF function to merge the duplicates of the Sales Rep column and according to this, we will add the Sales values for the duplicate names. For this purpose, we have added two columns; Helper 1, and Helper 2.

Applying IF Function to Merge Duplicate Rows in Excel

Steps:

  • Select the data range.
  • Go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort.

sort

Later, the Sort dialog box will open up.

  • Select the following options.
    • Sort by → Sales Rep column
    • Sort On → Cell Values
    • Order → A to Z
  • Press OK.

Later, the sales values with their corresponding representatives have been arranged in the following order.

sorted order

  • Type the following formula in cell D5.
=IF(B5=B4, D4+C5, C5)

Formula Breakdown

  • B5=B4 “Bill”= “Sales Rept”
  • IF(B5=B4, D4+C5, C5) IF( “Bill”= “Sales Rept”, D4+C5, C5) → becomes
    • IF( FALSE, D4&”, “&C5, 180) → as here the logical condition is FALSE so it will return only the 180 otherwise it will combine the value of a cell of the Helper 1 column and the value of the following cell of the Sales
      • Output $180

Applying IF Function to Merge Duplicate Rows in Excel

  • Press ENTER and drag down the Fill Handle tool.

In this way, you will be able to combine the sales values for the duplicate rows.

  • Now, write down the following formula in cell E5 and press ENTER.
=IF(B6<>B5, "Added Values","")

Formula Breakdown

  • IF(B6<>B5,” Added Values”,””) → becomes
    • IF(“Bill” <> “Bill”, “Added Values”,””) → returns
      IF(FALSE,” Added Values”,””) → as the values are equal and so IF will return a Blank.
      • Output → Blank
  • Drag down the Fill Handle tool.

Applying IF Function to Merge Duplicate Rows in Excel

After that, you will get Added Values for the duplicate rows.

added values

As we want to sort the following table based on the last column, we will remove the formulas and keep only values.

  • Select the Helper 1 and Helper 2 columns and press CTRL+C.

copy

  • Right-click and select the Paste Values option.

paste values

Later, the values of the last two columns will work only as values, not formulas.

Applying IF Function to Merge Duplicate Rows in Excel

  • Select the data range.
  • Go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort.

sort

Later, the Sort dialog box will open up.

  • Select the following options.
    • Sort by → Helper 2 column
    • Sort On → Cell Values
    • Order → Z to A
  • Press OK.

Then, the combined sales values with their sales persons will be gathered together.

Applying IF Function to Merge Duplicate Rows in Excel

As we didn’t need the last 5 rows, we removed them.

  • Select the unnecessary Sales and Helper 2 columns by pressing CTRL and then remove them.

select non-adjacent columns

Finally, you will get the unique list of the sales representatives along with their total sales from your initial data set.

Applying IF Function to Merge Duplicate Rows in Excel


Method-5: Implementing a VBA Code

The VBA codes also help merge duplicate rows in the worksheet. We’ll show you how to work with VBA code to consolidate duplicate rows in MS Excel.

Implementing VBA Code to Merge Duplicate Rows in Excel

Steps:

  • First of all, right-click on the Worksheet name “VBA”.

  • Choose View Code.

View Code

Later, a Microsoft Visual Basic Applications Module window will open.

  • Type the following code.
Sub same_rows_com()
Dim full_list As Range
Dim cell_V, modified_list As Variant
Dim Heading As String
Dim rep_sales As Integer
On Error Resume Next
Heading = "Merge Duplicates"
Set full_list = Application.Selection
Set full_list = Application.InputBox("Range", Heading, full_list.Address, Type:=8)
Set cell_V = CreateObject("Scripting.Dictionary")
modified_list = full_list.Value
For rep_sales = 1 To UBound(modified_list, 1)
cell_V(modified_list(rep_sales, 1)) = cell_V(modified_list(rep_sales, 1)) _
+ modified_list(rep_sales, 2)
Next
Application.ScreenUpdating = False
full_list.ClearContents
full_list.Range("A1").Resize(cell_V.Count, 1) = Application.WorksheetFunction _
.Transpose(cell_V.keys)
full_list.Range("B1").Resize(cell_V.Count, 1) = Application.WorksheetFunction. _
Transpose(cell_V.items)
Application.ScreenUpdating = True
End Sub

Here, we have declared full_list as Range and cell_V, modified_list as Variant, and used On Error Resume Next to ignore the error and continue or resume the code execution to the next cell.
The FOR loop is used for a range of rows starting from rep_sales = 1 and the UBOUND function will determine the size of the array.

Implementing VBA Code to Merge Duplicate Rows in Excel

  • Press F5.

After that, the Merge Duplicates (as we determined the name of this title) wizard will open.

  • Select the range and press OK.

select range

Finally, you will be able to merge the duplicates along with summing up the Sales values.

Implementing VBA Code to Merge Duplicate Rows in Excel

Then, we removed the unnecessary borders of the empty cells.
The final combined list will be like the following figure.

Read More: Combine Duplicate Rows and Sum the Values in Excel


Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet.

practice


Conclusion

In this article, we have discussed different ways to merge duplicate rows in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo