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

Merging Duplicate rows in Excel means combining duplicate entries of your Excel data for better presentation and organization.

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.

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.

After reading this article, you will learn how to merge duplicate rows in Excel using

– Consolidate command
– Subtotal feature after sorting the data
– Pivottable feature
– Using IF function on the sorted data
– Using a VBA code

Note: We have used Excel for Microsoft 365 version to complete this tutorial. But you can use any.

1. Using the 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.

Steps:

• Select the first cell of the range where you want the output.
• Go to the Data tab >> Data Tools group >> 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 box.
• Click on Add.

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

• Check the Left column option and press OK.

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

Read More: How to Combine Duplicate Rows in Excel without Losing Data

2. Applying Subtotal Feature

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

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.

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.

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

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.

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.

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

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

You can add borders for the last two rows.

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.

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.

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.

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

Then, the PivotTable will appear on the left side.

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.

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.

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

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.

Steps:

• Select the data range.
• Go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom 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.

• Type the following formula in cell D5.

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

• Drag down the Fill Handle tool.

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

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.

• Right-click and select the Paste Values option.

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

• Select the data range.
• Go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom 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.

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.

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

5. Implementing a VBA Code to Merge Duplicates

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.

Steps:

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

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

• Press F5.

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

• Select the range and press OK.

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

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.

Download Practice Workbook

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

• Merge Data from Duplicate Rows Based on Unique Column in Excel