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.

Download Workbook


6 Ways to Merge Duplicates in Excel

Here, we have used the following table for demonstrating the ways of merging duplicates in Excel.

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

merge duplicates in excel


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.

merge duplicates in excel

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

IF function

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

Sort by Product
Sort On Cell Values
Order A to Z

➤ Press OK

IF function

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

IF function

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

=IF(B5=B4,D4&", "&C5,C5)
  • B5=B4 → “Apple”= “Product”
  • IF(B5=B4, D4&”, “&C5, C5) IF( “Apple”= “Product”, D4&”, “&C5,C5) becomes
    IF( FALSE, D4&”, “&C5, “Robin”) → as here the logical condition is FALSE so it will return only the Salesperson’s name otherwise it will combine the value of a cell of the Combined Names column and the value of the following cell of the Salesperson column with the help of the Ampersand operator.
    Output → Robin

merge duplicates in excel

➤ Press ENTER
➤ Drag Down the Fill Handle tool

IF function

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

IF function

➤ Now, write the following formula in cell E5

=IF(B6<>B5,"useful","")
  • IF(B6<>B5,”useful”,””) IF(“Apple” <> “Apple”,”useful”,””) returns
    IF(FALSE,”useful”,””) → as the values are equal and so IF will return a Blank
    Output → Blank

IF function

➤ Press ENTER
➤ Drag Down the Fill Handle tool

IF function

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

IF function

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

merge duplicates in excel

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

IF function
➤ Choose the useful option and click OK

IF function

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

merge duplicates in excel


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.

merge duplicates in excel

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)

Merge & Center option

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

Merge & Center option

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

Merge & Center option

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

merge duplicates in excel


Method-3: Using Power Query 

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

merge duplicates in excel

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

power query

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

power query

After that, a Power Query Editor will appear

power query

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

power query

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

power query

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

merge duplicates in excel


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.

merge duplicates in excel

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

pivot table

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

pivot table

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.

pivot table

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

pivot table

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

pivot table


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.

merge duplicates in excel

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

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

consolidate option

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

consolidate option

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

merge duplicates in excel


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.

merge duplicates in excel

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

VBA Code

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

VBA Code

After that, a Module will be created.

VBA Code

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.

VBA Code

➤ 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

VBA Code

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

merge duplicates in excel


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.

practice


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.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo