The most common problem when working with a range of data that contains duplicate entries is when you want to combine the duplicate data and sum the values. There are various ways to combine duplicate rows and sum the values. They are as follows:
Using the Consolidate Function
The Consolidate Function is useful in consolidating multiple worksheets or rows in Excel. It is used to summarize multiple rows based on the duplicates. To use the Consolidate function, follow these steps :
1. Click the cell where you want to locate the results in your current worksheet
2. Select data, then click on Consolidate
3. In the Consolidate dialog box
- Select the sum from the function drop-down list
- Click on the icon with an arrow to choose the range you want to consolidate, then tap the "Add button" to add the reference to all references in The All References list.
- Look at the top row and left column from the use labels in the option.
4. After completing the settings, select Ok. The duplicates will be combined and summed.
Using the VBA code
The steps are as follows:
1. Hold down the ALR+F11 keys. The "Microsoft visual basic for applications window" will open
2. Click insert then Module and paste the VBA code in the module window. The VBA code: combine duplicate rows and sum the values
Sub CombineRows()
Dim lr As Long, r As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("A2:E" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
For r = lr To 3 Step -1
If Cells(r, 1).Value = Cells(r - 1, 1).Value Then
Range("B" & r, Range("A" & r).End(xlToRight)).Copy Destination:=Cells(r - 1, Columns.Count).End(xlToLeft).Offset(, 1)
Rows(r).Delete
End If
Next r
For r = 6 To Range("A1").CurrentRegion.Columns.Count Step 4
Cells(1, r).Resize(, 4).Value = Range("B1:E1").Value
Next r
Application.ScreenUpdating = True
End Sub
3. Tap the "F5 key" to run
the VBA code after selecting the range that you want to consolidate from the prompt box
4. Click Ok
The duplicate rows will be combined and added up
Note
The VBA code helps to consolidate duplicate rows in the worksheet. However, the original data will be destroyed hence the need to back up the copy of the data. To use the code, you will have to make a copy of the file to prevent data destruction. The code is only applied to two columns
Using Kutools for Excel
Follow these steps :
1. Install Kutools for Excel
2. Choose the range you want and click Kutools then 'merge and split' then Advanced combine Rows
3. Check 'My data has headers' from the Advanced combine Rows dialog
If the range has headers, then select the column name you want to combine the duplicates and click a primary key
4. Choose the column name you want to sum the values, then click calculate, sum, or any other calculations you need.
5. Click ok
The duplicates will be combined, and the corresponding data in another column will be summed up.
Combining corresponding rows based on duplicate values in another column using Kutools for Excel
To combine Rows based on duplicate values in another column, use these steps:
1. Choose the data range that you want to use
2. Click 'Kutools' then 'merge and split.'
3. Go to advanced combine Rows to enable the Advanced combine Rows dialog box
4. Click the column name you intend to combine other data from the Advanced combine Rows dialog box. Then click the primary key
5. Click on another column name that you want to combine the data, then click combine to select a separator to differentiate your combined values
6. Click ok
The values with the same column 1 or A will be combined.