How to combine duplicate rows in Excel 

Merge Duplicates Wizard 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

Pay attention to the Back up this worksheet checkbox. We recommend keeping this option selected as Excel doesn't let you cancel changes made by add-ins.

Note that hidden rows are still processed by the add-in.

Please also keep in mind that the tool inserts value results, 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

On 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.
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.

Do not forget 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

On 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, tick off the My table has 1 header row checkbox at the top and change the number of header rows if necessary. 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 the Next button.

Step 3: Pick columns with the values to merge

On 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 wish 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.

    On Step 2, we check columns A and B and on 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 on 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