How to Combine Duplicate Rows and Sum the Values in Excel

When managing large datasets, users often stumble upon duplicate rows containing different values. For example, the same product can be repeated twice in the sales report with different sales amounts. In that case, just removing the duplicate rows is not enough. We need to combine those rows to remove the duplicates, and the values are summed together. Excel has built-in functions, ensuring you generate a cleaner report with interactive dashboards and summaries.

The Pivot Table option is the most flexible and easiest-to-use method for summing up the values for the duplicate columns. Here, in this example, we have a Product Name column with some products appearing more than once. In the other columns, like Quantity Sold and Total Revenue, are projected different values for each duplicate one. We can use the Pivot Table option to summarize and combine these duplicated entries.

Steps:

➤ In your Excel file, select the entire table of the datasets.
➤ Go to the Insert tab and select the Pivot Table option from the left-most side of the window.

➤ A Create Pivot Table window appears. Ensure the range of the table is properly set and click OK.

➤ A new worksheet is opened with the PivotTable Fields on the right-hand side.
➤ From this pane, drag the column name with duplicate rows (e.g., Product Name) in the Rows area.
➤ Drag the column name with the different values (e.g., quantity sold and total revenue) in the values area.
➤ Ensure both columns are set to sum format in that area. They will be written as Sum To Column Name (e.g, Sum To Quantity Sold).

➤ This will automatically generate a new column in the new worksheet, removing the duplicate rows and adding the numeric values.

➤ You can format the table or the header names to make it report-ready.

If you are more into formulas, SUMIFS is better for you. Without distorting the layout and with hands-on control over it, you can use this function within your single sheet. This will not remove the duplicate rows from the first column. Rather, it will combine their values into one by summing them.

Steps:

➤ Copy the column containing the duplicated names (e.g, Product Name) and paste it beside or below your table.

➤ Give a proper header to the column beside it, and write the following formula in the first cell –

Here, the range of D2 to D11 consists of values of the duplicated cells (e.g, Quantities Sold) and A2 to A11 is the column containing the duplicated values. And, A14 is the first cell containing the new pasted column.

➤ Follow the same formula if you have more columns with different values for the same rows.

➤ Press Enter to display the combined value for each of the cells.
➤ Use Fill Handle or drag the cells to generate this for the entire column.

Data that needs to be regularly updated always requires an automated solution like Power Query. Combining the duplicate rows and summing the values is a one-click thing with this. Without any high level of Excel skills, you can easily create a Power Query combined table.

Steps:

➤ Open the Excel file and select the whole table or the columns you want to include in your combined table.
➤ Click on the Data tab -> From Table/Ranges.

➤ In the pop-up window, check the My table has headers, and click OK.

➤ This launches the Power Query window with the table loaded into it.

➤ Select the column that contains the duplicate rows. Here, it is the Product Name or column A.
➤ Go to the Home tab and click on Group By.

➤ The Group By dialog box appears. Mark the Advanced option for more options, where the column name you selected will also be displayed there.

➤  Below that, write your preferred name in the New Column Name.
➤ In the Operation, choose the SUM function from the drop–down menu. Select the column values you want to sum in the Column field.

➤If you want to combine the values of more than one column, click on the Add aggregation button. This will generate a new field for a new column. Fill it accordingly.

➤ Hit OK to proceed.
➤ This will remove the duplicates from the Product Name column and combine the values of Quantity sold and Total Sales in the repeated ones.

➤ Lastly, go to the Home tab and click Close and Load to load this table into a new worksheet.

If you’re more into internal Excel stuff, there’s no better thing than the VBA Macros. It has a powerful solution to reduce the duplicate rows and sum their values, based on your needs. Though it might not sound beginner-friendly, this method lets you get reliable and full control over your datasets.

Steps:

➤ Go to the Developers tab -> Visual Basic in your Excel file.
➤ This will launch the VBA editor. In the window, click Insert -> Module to write the code snippet.

➤ In the blank space, paste the following VBA script-

Sub CombineAndSumDuplicates()
    Dim dict As Object
    Dim ws As Worksheet, outputWs As Worksheet
    Dim lastRow As Long, i As Long
    Dim prodName As Variant
    Dim qty As Double, sales As Double
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Sheets("VBA Macro Method") ' Change  to your actual sheet name
    Set outputWs = ThisWorkbook.Sheets.Add
    outputWs.Name = “VBA Generated Sheet”
    ' Add headers
    outputWs.Range("A1").Value = "Product Name"
    outputWs.Range("B1").Value = "Total Quantity"
    outputWs.Range("C1").Value = "Total Sales"
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through data and sum duplicates
    For i = 2 To lastRow
        prodName = ws.Cells(i, 1).Value
        qty = ws.Cells(i, 4).Value
        sales = ws.Cells(i, 5).Value
        If dict.exists(prodName) Then
            dict(prodName)(0) = dict(prodName)(0) + qty
            dict(prodName)(1) = dict(prodName)(1) + sales
        Else
            dict.Add prodName, Array(qty, sales)
        End If
    Next i
    ' Output results
    i = 2
    For Each prodName In dict.Keys
        outputWs.Cells(i, 1).Value = prodName
        outputWs.Cells(i, 2).Value = dict(prodName)(0)
        outputWs.Cells(i, 3).Value = dict(prodName)(1)
        i = i + 1
    Next prodName
    MsgBox "Duplicate rows combined and values summed successfully!"
End Sub

➤ Click Save and save the file as macro-enabled or disabled according to your requirement.
➤ Close the window.
➤ In your Excel, go to the Developer tab and click on Macros.
➤ This will show a Macros window; select the function name you created. Here, it is CombineAndSumDuplicates (first line of the VBA script).

➤ Clicking on Run will create a new worksheet named VBA Generated Sheet or according to your VBA snippet. This will have only the distinct values with combined values.

Frequently Asked Questions

How can I combine duplicates across multiple columns and sum values?

You can combine duplicate data across multiple columns and sum their values using the Pivot Table and the SUMIFS function. For more advanced tasks, you can even use Power Query and VBA Macros.

Why is my SUMIFS not working when combining duplicates?

The SUMIFS function may not work due to range inconsistencies, containing extra leading or trailing spaces, or even incomplete logic. In either case, you must check your SUMIFS formula carefully. Instead of losing the workflow, you can also opt for other methods like Pivot Table.

Can I use a formula to combine and sum duplicates without helper columns?

To combine and sum the duplicates, you don’t always need the helper columns to combine and sum the duplicates. You can use the SUMIFS and UNIQUE formulas for faster conversion; it is flexible and doesn’t need any extra columns.

Can I merge text values for duplicates while summing the numbers?

Text values can also be merged while summing the number, depending on your method. For instance, Pivot Table does not provide this flexibility, but SUMIFS is handy in this case.

How can I prevent duplicates from being counted again after summing?

To prevent duplicates from being counted again, you can follow several methods. The most common one among them is the SUM(DISTINCT) formula. You can also use the SUMIFS formula if you have a helper column.

Final Thoughts

Combining duplicate rows and summing their values is not a mundane task. It is a regular while working with the large and complex datasets. We tried to simplify these processes with step-by-step methods and descriptions. From basic tools like Pivot Tables or SUMIFS function to advanced tools like VBA Macros and Power Query, we covered the key steps that could make your database accurate, cleaner, and an insightful analysis.

For a clear understanding, go through each step. Download the workbooks and practice on your own. In case of any confusion or problem, feel free to reach us to make the journey easier than ever.