how to merge duplicate rows in excel using formula

How to merge duplicate rows in excel using formula how to#

The above steps would insert a Pivot table in the selected destination cell. Select the location where you want the resulting Pivot Table to be inserted.In the Create PivotTable dialog box, make sure the Table/Range is correct.In the Tables group, click on PivotTable option.Suppose you have a data set as shown below where the country name repeats multiple times and you want to consolidate this data.īelow are the steps to create a Pivot table: 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. 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. Combine and Sum Data Using Pivot TablesĪ Pivot Table is the Swiss army knife of slicing and dicing data in Excel. you can also use this feature to consolidate data from multiple worksheets in the same workbook, and even multiple different workbooks.

How to merge duplicate rows in excel using formula how to#

In this example, I have shown you how to consolidate the data in a single data set in a worksheet. You can also choose other options such as Count or Average or Max/Min. I chose to get the SUM of the values from each record. In the end result, you get a unique list of countries along with the sales value from the original data set. The above steps would consolidate the data by removing the duplicate entries and adding the values for each country. Select the range A2:B9 (the data excluding the headers).Click on the range selection icon in the Reference field.In the Consolidate dialog box, select Sum from the function drop-down (if not already selected by default).In the Data Tools group, click on the Consolidate icon.Select the cell below the leftmost header.Copy the headers of the original data and paste it where you want the consolidated data.

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.

Suppose you have a data set as shown below where the country name repeats multiple times. The other method is to use a Pivot table and summarize the data (covered next in this tutorial). 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.

Combine and Sum Data Using Pivot TablesĬombine and Sum Data Using the Consolidate Option.

Combine and Sum Data Using the Consolidate Option.