Combine duplicate rows in Excel

How to merge Excel duplicate rows into one

Merge duplicate rows in excel into one

Merge Duplicates Wizard is an add-in for Microsoft Excel 2016, 2013, 2010 and 2007 specially designed for combining data from duplicate rows into one record.

Free Download
7-day fully functional version

How to work with Merge Duplicates Wizard: 2 min video

Step1: Select an Excel table

Open the table where you need to combine duplicate rows into one record and click the Merge Duplicates icon to run the tool.

Merge Duplicates Wizard icon

The add-in will pick the entire range with your data at start.

  • Another way to specify the range with your table is using the “Select range” icon in the add-in window.
  • You can also select the records right in the worksheet, the add-in will pick up your selection.
    Please note that the add-in will always get the entire range of data formatted as a table.
  • You can also type the range address in the Select your table field manually.
    Note. If you have hidden rows in your main and lookup tables, they will be processed.
Type the address of your table in this field

Once you start the add-in, you will see the step you are on at the top of the window.

The first step also lets you get a copy of your original sheet: tick the “Create a backup copy of the worksheet” checkbox to have it created. We recommend keeping this option selected as Excel doesn’t let you cancel changes made by add-ins.

Click Next to select your key columns for combining rows.

Step 2: Select key columns to check for duplicates

Key columns are the columns that will be checked for duplicate entries. You can select one or more columns to merge duplicate records into one. If you select more than one column, a record will be considered duplicate if values in all selected key columns are the same.

This step will show a list of all columns you have in your Excel sheet. If your table has a header row, you will see its values in the list.

  • You can control if the first row is perceived as a label or a data row using My table has headers checkbox at the top. Without the labels, you can look at the 1st row content to understand what kind of data the columns contain and select the right records to check for duplicates.
  • If you have blanks in your key column, select the Skip empty cells option to avoid merging them. Unselect the check box to consider blank values as duplicates.
  • If you have a lot of columns in your table, take advantage of the Select All button or the check-box next to the Columns label and all the boxes in the list will be checked. Then you can remove selection from the columns with unique values that you want to merge.
Tick the checkboxes next to key columns
Tip. If you have a lot of columns in your tables, 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 specify what records to combine in your Excel worksheet.

Step 3: Select columns with the values to merge

Choose the columns with unique values to combine into one cell on this step. You will see a list of columns that were not selected as key columns, a field for the delimiter, and a couple of advanced options.

  1. Tick the checkboxes next to the columns with the data you need to merge.
    Tip. If you have a long list of headers, take advantage of the check-box next to the Column Name label, or the Select All / Unselect All buttons. They let you instantly select and deselect all columns.
  2. Specify the delimiter that will separate the merged values. To use the same one in each column, click the Choose delimiter drop-down list at the top. You can either enter your own or select one from the predefined options. Standard delimiters include a semicolon, comma, space, and a line break. To use a different delimiter for some records in your Excel worksheet, enter or select it in the Delimiter field next to the column name in the list. Check the columns you need to merge
  3. The add-in also lets you deal with duplicates and blanks in the columns you are merging.
    • If the records you need to combine into one row may contain identical items, tick the Delete duplicate values checkbox to keep only unique values in your results.
    • Select the Skip empty cells option to avoid adding extra delimiters for blank cells.

Click Finish to see all duplicate rows perfectly merged in your Excel table.

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard
 
 
Excel add-ins and Outlook tools - Ablebits.com