How to combine duplicate rows in Excel

Start Merge Duplicates

On the Ablebits Data tab in the Merge group, click the Merge Duplicates icon:

Step 1: Select your table

In the first step, the add-in picks the entire range with your data:

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

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:

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.

Click Next to continue.

Step 3: Pick columns with the values to merge

In this step, select the columns with the entries to merge:

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:
  
• 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:
  
  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:
  
• 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:
  

  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:
    
  • If we select For unprocessed columns keep last rows data, the result will be:
    

Click Finish to get the duplicates in the selected columns merged:

How to save scenarios

Before proceeding to processing your data with Merge Duplicates in Step 3, click the Save scenario button:

In the tool's dialog that will show up, enter the name of your scenario and click OK:

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: