How to Merge Duplicate Rows in Excel (3 Effective Methods)

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


Download Practice Workbook

You can download the practice book from the following link.


3 Methods 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 a single time in the table together with his total sales. We will show three widely used methods for merging duplicate rows to consolidate our sample data.

Merge Duplicate Rows in Excel: Sample Dataset


1. Use the Consolidate Option to Merge Duplicate Rows

The Excel 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

Steps:

1. Select your data headers, copy and paste them in the location (E4:F4) where you want to show the consolidated data.

Merge Duplicate Rows Using Consolidate Option

2. Select the Cell E5 located just under the left-most header of the new table. Then go to the Data tab.

3. Now, go to the Data Tools group and click on Consolidate icon. A dialogue box will appear.

4. From the Function drop-down, select Sum (or any option you find useful for your task).

5. In the Reference field, click on the Range Selection icon and select the range of cells B5:C14. Don’t forget to select the Left column checkbox.

6. Press OK.

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

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


2. Use the Excel Pivot Table to Consolidate Duplicate Rows

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:

1. Click any cell in your data set (Here on Cell B5) and go to the Insert tab.

Merge Duplicate Rows in Excel Using Pivot Table

2. In the Tables group, select the PivotTable option.

3. The Create PivotTable dialog box will open up. Look at the Select a table or range field and look carefully if the selected range is correct. Choose Existing Worksheet.

4. Click on the Location icon and select the location (Here in Cell E4) where you want to place the resulting PivotTable. Then press OK.

A pivot table will appear in the selected Cell E4.

Merge Duplicate Rows in Excel

5. Click anywhere in the Pivot table.

Merge Duplicate Rows in Excel

The PivotTable dialog box will open up on the right side.

Merge Duplicate Rows in Excel

6. Mark the Sales Rep and Sales checkboxes. Drag the Sales Rep field in the Rows area and the Sales field in the Values area.

Merge Duplicate Rows in Excel

Finally, we have consolidated our data using the PivotTable tool.

Merge Duplicate Rows in Excel

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


Similar Readings


3. Use of Excel VBA Codes to Combine Duplicate Rows

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.

Note:

The original data will no longer exist in the sheet after we have used VBA Code. We’ll have to back up a copy of the data.

Steps:

1. First of all, right-click on the Worksheet name “Use of VBA Code”. Then click on View Code.

Merge Duplicate Rows in Excel

2. Microsoft Visual Basic Applications Module window will be opened.

Merge Duplicate Rows in Excel

3. Copy the following VBA codes and just paste them into the module window.

Option Explicit
Sub CombineRows()
Dim Rng As Range
Dim x1 As Variant
Dim xRng As Variant
Dim Title As String
Dim i As Integer
On Error Resume Next
Title = "Merge Duplicate Rows"
Set Rng = Application.Selection
Set Rng = Application.InputBox("Range", Title, Rng.Address, Type:=8)
Set x1 = CreateObject("Scripting.x1tionary")
xRng = Rng.Value
For i = 1 To UBound(xRng, 1)
x1(xRng(i, 1)) = x1(xRng(i, 1)) + xRng(i, 2)
Next
Application.ScreenUpdating = False
Rng.ClearContents
Rng.Range("A1").Resize(x1.Count, 1) = Application.WorksheetFunction.Transpose(x1.keys)
Rng.Range("B1").Resize(x1.Count, 1) = Application.WorksheetFunction.Transpose(x1.items)
Application.ScreenUpdating = True
End Sub

Your MS VBA Module will appear like this.

Merge Duplicate Rows in Excel

4. Now press F5 or click on the Run Sub/User Form icon and then click Run.

Merge Duplicate Rows in Excel

5. We’ll select the range of cells B5:C14 which we want to consolidate and press OK.

Merge Duplicate Rows in Excel

6. The duplicate rows are merged now and the sales values are added up for each unique Sales Rep.

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


Conclusion

Hope you will find all these methods instrumental. The workbook is there for you to download and practice yourself.  If you have any questions, comments, or any kind of feedback, please let me know in the comment box.


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