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: 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: 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:
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
