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:
Step 1: Select your table
On 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.
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.
Step 2: Choose key columns with duplicate records
On 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.
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
On 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:
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:
- 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:
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:
If we select For unprocessed columns keep last rows data, the result will be:
Tip. If you need to change your settings on the previous steps, click the Back button and make the necessary corrections.
If you wish the add-in to memorize the tables and options you selected, click the Save scenario
Click Finish to get the duplicates in the selected columns merged: