Skip to main content

In Excel, it's a common scenario to encounter a dataset with duplicate entries. Often, you might find yourself with a range of data where the key challenge is to efficiently combine these duplicate rows while simultaneously summing up the values in a corresponding column as following screenshot shown. In this context, we'll delve into several practical methods that can help you consolidate duplicate data and aggregate their associated values, enhancing both the clarity and utility of your Excel workbooks.


Combine duplicate rows and sum the values with the Consolidate function

The Consolidate is a useful tool for us to consolidate multiple worksheets or rows in Excel, with this feature, we can combine duplicate rows and sum up their corresponding values quickly and easily. Please do with the following steps:

Step 1: Select a Destination Cell

Choose where you want the consolidated data to appear.

Step 2: Access the Consolidate Function and set up the consolidation

  1. Click Data > Consolidate, see screenshot:
  2. In the Consolidate dialog box:
    • (1.) Select Sum from Function drop down list;
    • (2.) Click to select the range that you want to consolidate in the Reference box;
    • (3.) Check Top row and Left column from Use labels in option;
    • (4.) Finally, click OK button.

Result:

Excel will combine any duplicates found in the first column and sum their corresponding values in the adjacent columns as following screenshot shown:

Notes:
  • If the range doesn't include a header row, ensure to uncheck Top row from the Use labels in option.
  • With this feature, calculations can only be consolidated based on the first column (the leftmost one) of the data.

Combine duplicate rows and sum the values with a powerful feature – Kutools

If you have installed Kutools for Excel, its Advanced Combine Rows feature allows you to easily combine duplicate rows, providing options to sum, count, average, or execute other calculations on your data. Moreover, this feature isn't limited to just one key column, it can handle multiple key columns, making complex data consolidation tasks much easier.

Note: If you want to use this Advanced Combine Rows feature, please download and install Kutools for Excel first.

After installing Kutools for Excel, select the data range, and then click Kutools > Merge & Split > Advanced Combine Rows.

In the Advanced Combine Rows dialog box, please set the following operations:

  1. Click the column name that you want to combine duplicates based on, here, I will click Product, and then select Primary Key from the drop-down list in the Operation column;
  2. Then, select the column name you want to sum the values, and then select Sum from the drop-down list in the Operation column;
  3. As for the other columns, you can choose the operation you need, such as combining the values with a specific separator or performing a certain calculation; (this step can be ignored if you have only two columns)
  4. At last, you can preview the combined result then click OK button.

Result:

Now, the duplicate values in the key column are combined, and other corresponding values are summed up as following screenshot shown:

Tips:
  • With this useful feature, you can also combine rows based on duplicate cell value as following demo shown:
  • This feature supports Undo, if you want to recover your original data, just press Ctrl + Z.
  • To apply this feature, please download and install Kutools for Excel first.

Combine duplicate rows and sum the values with the Pivot Table

Pivot Table in Excel provide a dynamic way to rearrange, group, and summarize data. This functionality becomes incredibly useful when you are faced with a dataset filled with duplicate entries and need to sum corresponding values.

Step 1: Creating a Pivot Table

  1. Select the data range. And then, go to the Insert tab, and click Pivot Table, see screenshot:
  2. In the popped-out dialog box, choose where you want the Pivot Table report to be placed, you can put it to a new sheet or existing sheet as you need. Then, click OK. See screenshot:
  3. Now, a Pivot Table is inserted in the selected destination cell. See screenshot:

Step 2: Configuring the Pivot Table:

  1. In the PivotTable Fields pane, drag the field containing duplicates to the Row area. This will group your duplicates.
  2. Next, drag the fields with the values you want to sum to the Values area. By default, Excel sums the values. See the demo below:

Result:

The Pivot Table now displays your data with duplicates combined and their values summed up, offering a clear and concise view for analysis. See screenshot:


Combine duplicate rows and sum the values with VBA code

If you are interested in VBA code, in this section, we will give a VBA code to consolidate duplicate rows and sum the corresponding values in other columns. Please do with the following steps:

Step 1: Open the VBA sheet module editor and copy the code

  1. Hold down the ALT + F11 keys in Excel to open the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Combine duplicate rows and sum the values
    Sub CombineDuplicateRowsAndSumForMultipleColumns()
    'Update by Extendoffice
        Dim SourceRange As Range, OutputRange As Range
        Dim Dict As Object
        Dim DataArray As Variant
        Dim i As Long, j As Long
        Dim Key As Variant
        Dim ColCount As Long
        Dim SumArray() As Variant
        Dim xArr As Variant
        Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8)
        If SourceRange Is Nothing Then Exit Sub
        ColCount = SourceRange.Columns.Count
        Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8)
        If OutputRange Is Nothing Then Exit Sub
        Set Dict = CreateObject("Scripting.Dictionary")
        DataArray = SourceRange.Value
        For i = 1 To UBound(DataArray, 1)
            Key = DataArray(i, 1)
            If Not Dict.Exists(Key) Then
                ReDim SumArray(1 To ColCount - 1)
                For j = 2 To ColCount
                    SumArray(j - 1) = DataArray(i, j)
                Next j
                Dict.Add Key, SumArray
            Else
                xArr = Dict(Key)
                For j = 2 To ColCount
                    xArr(j - 1) = xArr(j - 1) + DataArray(i, j)
                Next j
                Dict(Key) = xArr
            End If
        Next i
        OutputRange.Resize(Dict.Count, ColCount).ClearContents
        i = 1
        For Each Key In Dict.Keys
            OutputRange.Cells(i, 1).Value = Key
            For j = 1 To ColCount - 1
                OutputRange.Cells(i, j + 1).Value = Dict(Key)(j)
            Next j
            i = i + 1
        Next Key
        Set Dict = Nothing
        Set SourceRange = Nothing
        Set OutputRange = Nothing
    End Sub
    

Step 2: Execute the code

  1. After pasting this code, please press F5 key to run this code. In the prompt box, select the data range that you want to combine and sum. And then, click OK.
  2. And in the next prompt box, select a cell where you will output the result, and click OK.

Result:

Now, the duplicate rows are merged, and their corresponding values have been summed up. See screenshot:


Combining and summing duplicate rows in Excel can be simple and efficient. Choose from the easy Consolidate function, the advanced Kutools, the analytical Pivot Tables, or the flexible VBA coding to find a solution that suits your skills and needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!


Related Articles:

  • Combine multiple rows into one based on duplicates
  • Maybe, you have a range of data, in the Product name column A, there are some duplicate items, and now you need to remove the duplicate entries in column A but combine the corresponding values in column B.How could aove this task in Excel?
  • Vlookup and return multiple values without duplicates
  • Sometimes, you may want to vlookup and return multiple matched values into a single cell at once. But, if there are some repeated values populated into the returned cells, how could you ignore the duplicates and only keep the unique values when returning all matching values as following screenshot shown in Excel?
  • Combine rows with same ID/name
  • For example, you have a table as below screenshot shown, and you need to combine rows with the order IDs, any ideas? Here, this article will introduce two solutions for you.
Comments (30)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Em planilha sem cálculo número, onde eu tenho uma lista de prestadores de serviço para determinadas empresas, como faço para deixar classificado por empresas, por ordem de empresas? Se é possível.
Na planilha eu tenho, o nome da pessoa, razão social e empresa. Neste caso algumas empresas repetem, gostaria de classificar automático, sem precisar refazer um por um.
This comment was minimized by the moderator on the site
is there a way to save the specific merging and combining settings so that i can reuse them for future workbooks?
This comment was minimized by the moderator on the site
Thanks for your help.

This comment was minimized by the moderator on the site
SN SAD No Unit Item No Description Qty CIF_Value ID_EXD AID CSF ARF ECS RCF RDF IFT IDP AIT VAT
1 M200 UNT 1 Pen 194 500 50 0 0 0 0 0 0 0 0 0 65
2 M200 UNT 2 Pencil 241 250 25 0 0 0 0 0 0 0 0 0 32.5
3 M200 UNT 3 Cutter 204 400 40 0 0 0 0 0 0 0 0 0 52
4 M200 UNT 4 Copy 171 600 60 0 0 0 0 0 0 0 0 0 78
5 M300 KGM 1 Cup 220 250 25 0 500 0 0 0 0 0 0 0 32.5
6 M300 KGM 2 Plate 40 350 35 155 0 0 0 0 0 0 0 0 45.5
7 M300 UNT 3 Bottle 2 150 15 131 0 0 0 0 0 0 0 0 19.5
8 M300 UNT 4 Glass 2 90 9 34 0 0 0 0 0 0 0 0 11.7
9 M400 null 1 Shirt 20 800 80 0 0 0 0 0 0 0 0 0 104
10 M400 KGM 2 Pant 5 5000 500 0 0 0 0 0 0 0 0 0 650
11 M400 null 3 Shoe 12 7200 720 0 0 0 0 0 0 0 0 0 936
12 M400 MTR 4 Sandle 40 1600 160 0 0 0 0 0 0 0 0 0 208
13 M400 UNT 5 Belt 100 2000 200 0 0 0 0 0 0 0 0 0 260
how to sum cif value and remove duplicate No. (SAD No.) Please help me.
This comment was minimized by the moderator on the site
I am sooooo happy & glad with your tips. Allah bless you.
This comment was minimized by the moderator on the site
Sub MergeSameCells()
Application.DisplayAlerts = False

Dim rng As Range

MergeCells:

For Each rng In Selection
If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
Range(rng, rng.Offset(1, 0)).Merge
GoTo MergeCells
End If
Next

End Sub
This comment was minimized by the moderator on the site
An absolute mess of an explantation. Thanks for the effort but it did nothing to help.
This comment was minimized by the moderator on the site
LOVE IT!!! YOUR SAVE MY LIFE!!
This comment was minimized by the moderator on the site
Ifsum=(columns include,start point row,sum column)
Example ifsum=(A:D,B:2,D:D)
WAY EASIER!
This comment was minimized by the moderator on the site
Hi Am chinnaraju

can u please assist for this. Any one?

=VLOOKUP(M5,E:F,2,)


Thanks in advance.
This comment was minimized by the moderator on the site
Needs to be:
=VLOOKUP(M5,E:F,2,FALSE)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations