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.

Merging Duplicates in Excel

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.

Merging Duplicates in Excel

Procedure:

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

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.
Merging Duplicates in Excel

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

Merging Duplicates in Excel
  • 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.

Merging Duplicates in Excel

2. Utilizing Subtotal Feature

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

Merging Duplicates in Excel

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.
Merging Duplicates in Excel

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.
Merging Duplicates in Excel

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

Merging Duplicates in Excel
  • Go to the Data tab >> Outline group >> Subtotal after selecting the data range.
Merging Duplicates in Excel

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.
Merging Duplicates in Excel

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

Merging Duplicates in Excel
  • 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.

Merging Duplicates in Excel

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

Merging Duplicates in Excel

The final two rows can have borders added to them.

Merging Duplicates in Excel

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.

Merging Duplicates in Excel

Procedure:

  • Once the cell range has been chosen, select Insert > PivotTable.
    Merging Duplicates in Excel

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.
    Merging Duplicates in Excel

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

Merging Duplicates in Excel
  • Drag Sales to the Values area and Persons to the Rows area.

The Pivot Table will then show up on the left.

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

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

Merging Duplicates in Excel

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.

Merging Duplicates in Excel

Procedure:

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

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.
Merging Duplicates in Excel

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

Merging Duplicates in Excel
  • 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-
    Merging Duplicates in Excel
  • To drag the Fill Handle tool, press ENTER.
    Merging Duplicates in Excel
  • You can then merge the sales values in the duplicate rows.
    Merging Duplicates in Excel
  • 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.

Merging Duplicates in Excel

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

Merging Duplicates in Excel

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.
    Merging Duplicates in Excel
  • 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.
Merging Duplicates in Excel

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.
    Merging Duplicates in Excel
  • The total sales values of every salesperson will then be collected.
    Merging Duplicates in Excel
  • We took out the final four rows since we didn't require them.
    Merging Duplicates in Excel
  • Holding down the CTRL key will select and remove the Sales and Helper 2 columns.
    Merging Duplicates in Excel
  • When everything is said and done, you will have your initial data set's unique list of sales Persons and their total sales.
    Merging Duplicates in Excel

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.

Merging Duplicates in Excel

Procedure:

  • Initially, perform a right-click in the worksheet named "VBA".
    Merging Duplicates in Excel
  • 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.

Merging Duplicates in Excel
  • Hit the F5 key
  • Press OK after choosing the range.
Merging Duplicates in Excel
  • Then, it will merge duplicate values as shown in the below picture.
Merging Duplicates in Excel
  • Next, the empty cells' unnecessary borders were removed.
  • The combined list at the end will look something like this.
Merging Duplicates in Excel
Next TopicExcel 365