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
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.
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:
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:
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:
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:
Scenarios
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:
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.