How to Merge Duplicates in Excel (6 Ways)

If you are looking for some of the easiest ways to merge duplicates in Excel, then you are in the right place. It can be helpful for a dataset containing a large number of duplicates to merge them and make the dataset clean and tidy. So, let’s get into the main article.

How to Merge Duplicates in Excel: 6 Ways

Here, we have used the following table to demonstrate the ways of merging duplicates in Excel.

For creating the article, we have used Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: Using IF Function to Merge Duplicates in Excel with Texts

In this section, we will use the IF function to merge the duplicates of the Product column and according to this, we will combine the Salesperson’s names for the duplicate products.
For this purpose, we have added two columns; Combined Names, Helper.

Step-01:
➤ Select the data range
➤ Go to Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.

Then, the Sort dialog box will open
➤ Select the following

Sort by → Product
Sort On → Cell Values
Order → A to Z

➤ Press OK

After that, it will sort the names of the products from A to Z

Step-02:
➤ Type the following formula in cell D5.

➤ Press ENTER.
➤ Drag Down the Fill Handle tool.

In this way, you will be able to combine the names of the Salesperson for the duplicate rows.

➤ Now, write the following formula in cell E5.

➤ Press ENTER.
➤ Drag Down the Fill Handle tool.

After that, you will get useful for the duplicate rows.

Step-03:
➤ Select the data range
➤ Go to Home Tab >> Editing Group >> Sort & Filter Dropdown >> Filter Option

Then, the filter signs will appear in every column of the table.
➤ Select the dropdown sign of the Helper column.

➤ Choose the useful option and click OK.

Result:
In this way, you will be able to merge the duplicate rows of the Product column and combine the names of the Salesperson according to the duplicate rows.

Method-2: Using Merge & Center Option to Merge Duplicates in Excel

You can easily merge the duplicates by using the Merge & Center or Merge Cells Option.

Step-01:
At first, we have followed Step-01 of Method-1 to sort the product names from A to Z
➤ Now, select the two cells containing Apple and go to Home Tab >> Merge & Center Group >> Merge & Center Option (or Merge Cells Option)

Then, a message box will appear which says that this action will only keep the upper-left value.
➤ Press OK

After that, you will be able to merge the first two cells containing Apple.

Result:
Similarly, do the same for other duplicate cells and get the following table.

Method-3: Using Power Query 

Here, we will use Power Query to merge the duplicate rows of the Product column.

Step-01:
➤ Go to Data Tab >> FromTable/Range option

Then, the Create Table dialog box will open up.
➤ Select the data range.
➤ Click the My table has headers option and press OK.

After that, a Power Query Editor will appear

➤ Select the Product column where you have duplicate values.
➤ Go to Home Tab >> Group By Option.

Afterward, Group By wizard will pop up.
➤ Select the following options

Basic
Product (the name of the column)
New column name → Count
Operation → Count Rows

➤ Press OK

Result:
Then, you will be able to merge the duplicates and in the Count column, it will show how many rows have been merged.

Method-4: Using Pivot Table Option to Merge Duplicates in Excel

In this section, we will use the Pivot Table option to merge the duplicate rows and sum up their corresponding Sales values.

Step-01:
➤ Go to Insert Tab >> PivotTable option.

Then, the PivotTable from table or range dialog box will open up.
➤ Select the data range.
➤ Click the New Worksheet option and press OK.

After that, a new sheet will appear where you have the two portions, PivotTable1 on the left side and PivotTable Fields on the right side.

➤ Drag Product to the Rows area and Sales to the Values area.

Result:
After that, you will merge the duplicates along with sum up the Sales values.

Method-5: Using Consolidate Option to Merge Duplicates in Excel

You can use the Consolidate option to merge the duplicate rows and sum up their corresponding Sales values.

Steps:
➤ Select the cell where you want to have the output.
➤ Go to Data Tab >> Data Tools Dropdown >> Consolidate Option.

Then, Consolidate wizard will open.
➤ Select Sum (or any other function) as Function, data range as Reference, and click on Add.
➤ Choose the Top row and Left column options and finally press OK.

Then, you will get the merged values in your selected area.

Finally, we have used borders for our new consolidated table.

Method-6: Using VBA Code to Merge Duplicates in Excel

You can use a VBA code to merge the duplicate rows and sum up their corresponding Sales values.

Step-01:
➤ Go to Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

After that, a Module will be created.

Step-02:
➤Write the following code.

Sub MergeDuplicates()

Dim Rng As Range
Dim d As Variant
Dim y As Variant

On Error Resume Next

Set Rng = Application.Selection
TitleId = "Merge Duplicates in Excel"
Set Rng = Application.InputBox("Range", TitleId, Rng.Address, Type:=8)
Set d = CreateObject("Scripting.Dictionary")
y = Rng.Value

For i = 4 To UBound(y, 1)

d(y(i, 1)) = d(y(i, 1)) + y(i, 2)

Next

Application.ScreenUpdating = False

Rng.ClearContents
Rng.Range("A1").Resize(d.Count, 1) = Application.WorksheetFunction.Transpose(d.keys)
Rng.Range("B1").Resize(d.Count, 1) = Application.WorksheetFunction.Transpose(d.items)

Application.ScreenUpdating = True

End Sub

Here, we have declared Rng as Range and d, y 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 i = 4 (as our data table started from row number 4) and the UBOUND function will determine the size of the array.

➤ Press F5

After that, the Merge Duplicates in Excel (as we determined the name of this title) wizard will open.
➤ Select the range and press OK

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

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we have tried to cover the easiest ways to merge duplicates in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Merge Duplicates in Excel: Knowledge Hub

• Merge Duplicate Rows in Excel
• Combine Duplicate Rows in Excel without Losing Data
• Merge Data from Duplicate Rows in Excel Based on Unique Column
• How to Combine Duplicate Rows and Sum the Values in Excel

<< Go Back to Duplicates in Excel | Learn Excel