How to combine duplicate rows in Excel 

Merge Duplicates is an add-in for Microsoft Excel specially designed for combining data from duplicate rows into one.

Video: How to work with Merge Duplicates

Before you start

Before running the add-in, take account of the following:

  • We recommend keeping the Back up this worksheet checkbox selected as Excel doesn't let you cancel changes made by add-ins.
    Clear this checkbox if you do not want to keep the original data.
  • Hidden rows are processed by the add-in.
  • The tool inserts the results as values, not formulas.

How to use Merge Duplicates

Start Merge Duplicates

On the Ablebits Data tab in the Merge group, click the Merge Duplicates icon: Start Merge Duplicates for Excel.

Step 1: Select your table

In the first step, the add-in picks the entire range with your data: Select your table to merge duplicates.

  • To expand the selection into the current table or select a different range, choose the corresponding icon on the right side of the Select your table box.
  • You can also select the records right in the worksheet, the add-in will pick up your selection.
  • Another option is to type the range address in the Select your table field manually. Select the range in which to merge duplicates.
Note. If your data is formatted as a table, the add-in will always get the entire table.
Note. If you have hidden rows in your table, they will be processed.

Remember to tick the Back up this worksheet checkbox to have a copy of your data.

Click Next.

Step 2: Choose key columns with duplicate records

In this step, you can see a list of the columns your range contains: Select key columns with duplicates to merge.

Pick the columns where you want to find duplicate entries. If you select more than one column, a record will be considered duplicate if values in all the selected key columns are the same.

  • If you have header rows, the My table has 1 header row checkbox at the top is selected by default. You can change the number of header rows by clicking on 1 header row.
  • If you do not have labels, look at 1st row content to understand what kind of data the columns contain.
  • Select the Skip empty cells option if you have blanks in your key columns and don't want to merge them. Unselect the checkbox to consider blank values as duplicates.
Tip. If you have a lot of columns in your table, you can expand the wizard window by dragging the bottom right corner down and to the right until you get a suitable size.

Click Next to continue.

Step 3: Pick columns with the values to merge

In this step, select the columns with the entries to merge: Pick columns with the values to merge with the Merge Duplicates tool.

Tick the checkboxes next to the columns with the data you need to combine and take advantage of the advanced options:

  • Check Delete duplicate values if the records you need to combine may contain identical items but you want to keep only unique values in your results: Merge duplicates and delete duplicate values.
  • Select the Skip empty cells option to avoid adding extra delimiters to blank cells.
  • To get the merged values sorted, opt for Sort merged values.
    Note. The add-in sorts merged values as text strings.
  • To use the same delimiter in each column, click the Choose delimiter drop-down list at the top. You can either enter your own separator or select one from the predefined options. Standard delimiters include a semicolon, comma, period, space, and a line break. Besides, you can select one of the following aggregate functions: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.P, STDEV.S, or SUM: Select delimiters for merged values. Choose aggregate functions.
    Note. The tool uses the standard Excel functions (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.P, STDEV.S), so if you face certain difficulties, please consult a corresponding section here.
  • If you want to have different delimiters for each column, enter or select the needed ones in the Delimiter field next to the column name: Choose a different delimiter for each column.
  • If you don't want the add-in to process some of the columns your range contains but want to see their first or last row values in the resulting table, pick the option of interest from the drop-down menu next to For unprocessed columns.

    For instance, if this is your table: Simple dataset.

    In Step 2, we check columns A and B and in Step 3—column C.

    • If we select For unprocessed columns keep first rows data, the result will be: Simple dataset.
    • If we select For unprocessed columns keep last rows data, the result will be: Simple dataset.
Tip. If you need to change your settings in the previous steps, click the Back button and make the necessary corrections.

Click Finish to get the duplicates in the selected columns merged: Merge Duplicates Wizard for Excel

Scenarios

How to save scenarios

Before proceeding to processing your data with Merge Duplicates in Step 3, click the Save scenario button: Save your scenario. In the tool's dialog that will show up, enter the name of your scenario and click OK: Name your scenario. A message saying that your scenario has been saved will appear. Click OK.

How to run scenarios

On the ribbon, click the Merge Duplicates icon. Then choose both the scenario that you want to run and the table that is going to be used and click the Start button: Choose the necessary scenario.

Note. For a saved scenario to work for the current worksheet, the structure of your current table must be the same as that of the table in the scenario.