Merging Duplicates in Excel

In Excel, merging duplicate rows refers to combining entries from different worksheets to improve organization and presentation. We frequently need to combine duplicate rows in Excel when working with it at home, at work, in retail stores, or corporate settings. Various easy-to-use and efficient methods exist to integrate duplicate worksheet rows and total the results in Excel. We will now illustrate and provide appropriate examples for five of them.

Let's say we have an Excel worksheet with a data set that includes the sales information of multiple sales representatives. To ensure that each sales representative and his total sales are listed in the table only once, we must combine the data sets.

To combine our sample data, we will demonstrate five popular techniques for combining duplicate rows. You will understand how to combine duplicate rows in Excel after reading this tutorial by using:

1. Consolidate command
2. Subtotal feature following data sorting
3. Pivot table feature
4. Applying the IF function to the sorted data
5. Utilizing VBA code

1. Using Excel's Consolidate Option to Combine Duplicate Rows

Information from several rows, worksheets, or workbooks can be consolidated into one location using the Consolidate option. It assists you in compiling the data from your data table's various locations. We'll examine how this tool helps us resolve our issues step by step. On the right side, we created a table to collect the consolidated data.

Procedure:

• Choose the first cell in the Range that contains the desired output.
• Select Consolidate from the Data Tools group under the Data tab.

Then, the Consolidate dialog will launch.

• Select Sum (or any other option that seems appropriate for your task) in the Function drop-down menu, then select the Range within the Reference box.
• Click Add.

Our chosen Range will then show up within the entire references box.

• After selecting the Left column option, click OK.

Upon completion, you will receive a distinct roster of sales representatives plus an account summary derived from your original data set.

2. Utilizing Subtotal Feature

Here, the sales values for the duplicate Persons will be combined using the Subtotal feature.

Procedure:

We must first sort the names alphabetically to keep the duplicate titles together.

• Choose the Range, and then go to the Home tab; in that, go to Sort & Filter and select the Custom Sort option, i.e., Home tab >> Sort & Filter dropdown >> Custom Sort.

The Sort dialogue box is going to open later.

• Choose one of the following options:
  • Sort by→ Persons column
  • Sort on→ values
  • Order→ A to Z
• Click the OK button.

The following order was later created by placing the Sales values in relation to their respective Persons.

• Go to the Data tab >> Outline group >> Subtotal after selecting the data range.

A subtotal dialog box will appear after that.

• In the fields where they are designated, check the following column names and options.
  • A change in → Persons
  • Use function → Sum
  • Add subtotal to → Sales
• To replace the current subtotals and summary with the data options below, click on them and then click OK.

Every Persons's sales will subsequently be combined and totalled after being grouped.

• To reduce the two distinct sales values to one value, click the minus(-) sign next to Edward Total, as shown in the above image.

Thus, it's evident that Edward's duplicate rows have been combined in this instance.

We have completed this task for other representatives of sales in a similar manner.

The final two rows can have borders added to them.

3. Using a Pivot Table in Excel to Combine Duplicate Rows

A pivot table is one of the best tools for aggregating, combining, and examining data in Microsoft Excel. We'll demonstrate how to use this tool to merge duplicate rows and accomplish our goals using this method.

Procedure:

• Once the cell range has been chosen, select Insert > PivotTable.
  

It will open the Create PivotTable dialog box.

You can see that the Table/Range field now displays your selected range.

• After selecting New Worksheet, click OK.
  

Consequently, you will be redirected to a new sheet with the PivotTable on the left and the PivotTable Fields on the right.

• Drag Sales to the Values area and Persons to the Rows area.

The Pivot Table will then show up on the left.

• Afterwards, we added borders and modified the cell formatting.
  
• Select Options from the PivotTable dropdown menu under the Analyze tab to disappear the total value.
  
• The PivotTable Options screen will then appear.
• Unclick the total options listed below under the Totals and Filters tab.
• Click "OK."
  

The following table will show up, with the rows for duplicate people merged.

4. Using the IF Function in Excel to Combine Duplicate Rows

To add the sales values for the duplicate names, we will first use the IF function in this section to merge the duplicates of the Persons column. Helper 1 and 2 are the two columns we added for this purpose.

Procedure:

• Choose the range of data
• Select the range and in Editing Group under the Home Tab, Filter & Sort Dropdown >> Custom Sort.
  

The Sort dialog box is going to appear afterwards.

• Choose one of the upcoming options.
  • Sort by → Person column
  • Sort on →Values
  • Order to → A to Z.
• Press ok.

The following order has since been created for the sales values and the corresponding Persons.

• Enter this formula in cell D5: =IF(K5=K4, M4+L5, L5)

Formula Explanation

• Persons = Edward → K5 = K4
• IF(K5=K4, M4+L5, L5) → IF("Edward"= "Persons", M4+L5, L5) → becomes IF(FALSE, M4&", "&L5, 180) → As the logical condition in this case is FALSE, it will only return 180; otherwise, it combines the value of a Helper 1 column cell with the value of the next cell in the Sales Output →. $180-
  
• To drag the Fill Handle tool, press ENTER.
  
• You can then merge the sales values in the duplicate rows.
  
• Click ENTER after entering this formula in cell N5: =IF(K6<>K5, "Added Values","")

Formula Explanation

• Because the values are equal, IF(M6<>M5," Added Values","") → becomes IF("Edward" <> "Edward", "Added Values","") → returns IF(FALSE," Added Values","") → Empty
• Output → Empty

The Filling Handle tool can be dragged to get the remaining values.

You will then receive Added Values of the rows that are duplicates.

We will remove the formulas and retain the values in the following table since we need to sort it according to the final column.

• Click CTRL+C after selecting the Helper 1 and Helper 2 column.
  
• For the Paste Values option, use a right-click.
• The final two columns' values will eventually only function as values-not as formulas.
• Select the data range.
• Select Custom Sort from the Sort & Filter Dropdown menu under the Home tab after editing the group.

The Dialog box for Sorting will open later.

• Select the choices below.
  • Sort by → Helper 2
  • Sort on → Values
  • Order to → Z to A
• Click OK.
  
• The total sales values of every salesperson will then be collected.
  
• We took out the final four rows since we didn't require them.
  
• Holding down the CTRL key will select and remove the Sales and Helper 2 columns.
  
• When everything is said and done, you will have your initial data set's unique list of sales Persons and their total sales.
  

5. Using a VBA Code to Combine Duplicate

Additionally, the VBA codes facilitate the worksheet's merging of duplicate rows. We'll walk you through the process of using VBA code in MS Excel to combine duplicate rows.

Procedure:

• Initially, perform a right-click in the worksheet named "VBA".
  
• Select "View Code."
• View Code Afterwards: The Microsoft Visual Basic Application Module window will open.
• Enter the code below.

Code:

In this case, whole_list is declared as Range & cell_V, modified_list is displayed as Variants, and On Error Resume Next is used for ignoring the error and carrying out the code execution into the next cell.

The UBOUND function determines the size of the array, and the FOR loop applies for a range of rows beginning with Sales_rep = 1.

• Hit the F5 key
• Press OK after choosing the range.

• Then, it will merge duplicate values as shown in the below picture.

• Next, the empty cells' unnecessary borders were removed.
• The combined list at the end will look something like this.