Masum Mahdy
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.
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.
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.
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.
5. Click anywhere in the Pivot table.
The PivotTable dialog box will open up on the right side.
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.
Finally, we have consolidated our data using the PivotTable tool.
Read More: How to Merge Rows in Excel Based on Criteria (Easiest Ways)
Similar Readings
- How to Combine Duplicate Rows in Excel without Losing Data (6 Methods)
- How to Merge Rows with Comma in Excel (4 Quick Methods)
- Excel Combine Rows with Same ID (3 Quick Methods)
- Convert Multiple Rows to A Single Column in Excel (2 ways)
- How to Merge Rows in Excel without losing Data (5 Ways)
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.
2. Microsoft Visual Basic Applications Module window will be opened.
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 SubYour MS VBA Module will appear like this.
4. Now press F5 or click on the Run Sub/User Form icon and then click Run.
5. We’ll select the range of cells B5:C14 which we want to consolidate and press OK.
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.
