How to Combine Duplicate Rows and Sum the Values in Excel

As a part of my full-time job a few years ago, one of the things I had to deal with was to combine data from different workbooks shared by other people.

And one of the common tasks was to combine the data in such a way that there were no duplicate records.

For example, below is a dataset that multiple records for the same region.

And the final result needs to be a consolidated dataset where each country is reported only once.

In this tutorial, I will show you how to combine duplicate rows and sum the values to create a single consolidated dataset.

Combine and Sum Data Using the Consolidate Option

If all you need to do is consolidate data and add all the values for the repeating records, it’s best to use the consolidate feature in Excel.

The other method is to use a Pivot table and summarize the data (covered next in this tutorial).

Suppose you have a data set as shown below where the country name repeats multiple times.

While these are unique records as the sales value is different, for reporting purposes you may want to remove multiple instances of the same country and show the sales value as one consolidated sum.

Below are the steps to do this:

1. Copy the headers of the original data and paste it where you want the consolidated data
2. Select the cell below the leftmost header
3. Click the Data tab
4. In the Data Tools group, click on the Consolidate icon
5. In the Consolidate dialog box, select Sum from the function drop-down (if not already selected by default)
6. Click on the range selection icon in the Reference field.
7. Select the range A2:B9 (the data excluding the headers)
8. Select the Left column checkbox
9. Click Ok

The above steps would consolidate the data by removing the duplicate entries and adding the values for each country.

In the end result, you get a unique list of countries along with the sales value from the original data set.

I chose to get the SUM of the values from each record. You can also choose other options such as Count or Average or Max/Min.

In this example, I have shown you how to consolidate the data in a single data set in a worksheet. you can also use this feature to consolidate data from multiple worksheets in the same workbook, and even multiple different workbooks.

Combine and Sum Data Using Pivot Tables

A Pivot Table is the Swiss army knife of slicing and dicing data in Excel.

It can easily give you a summary which is a combined data set with no duplicates and the values that are the sum of all the similar records, and do a lot more.

The downside of this method as compared to the previous one is that this one takes more clicks and a few more seconds compared to the previous one.

Suppose you have a data set as shown below where the country name repeats multiple times and you want to consolidate this data.

Below are the steps to create a Pivot table:

1. Select any cell in the dataset
2. Click the Insert tab
3. In the Tables group, click on PivotTable option
4. In the Create PivotTable dialog box, make sure the Table/Range is correct
5. Click on the Existing Worksheet
6. Select the location where you want the resulting Pivot Table to be inserted.
7. Click OK

The above steps would insert a Pivot table in the selected destination cell.

Now, we can do all sorts of things with a pivot table – including consolidating our data set and removing the duplicates.

Below are the steps to do this:

1. Click anywhere in the pivot table area and it would open the pivot table pane on the right
2. Drag the put the Country field into the Row area
3. Drag and put the Sales field into the Values area

The above steps summarize the data and give you the sum of sales for all the countries.

If this is all that you want and you have no need for a pivot table, you can copy the data and paste it as values somewhere else and delete the pivot table.

This will also help you reduce the size of your Excel workbook.

So these are two quick and easy methods that you can use to consolidate the data where it would combine the duplicate rows and sum all the values in those records.

I hope you found this tutorial useful!

You may also like the following Excel tutorials:

• Combine Data From Multiple Worksheets into a Single Worksheet in Excel
• Combine Data from Multiple Workbooks in Excel
• How to Combine Cells in Excel
• How to Combine Multiple Excel Files Into One Excel Workbook
• How to Combine Duplicate Rows and Sum the Values in Excel