Combine duplicate rows in Excel

How to merge Excel duplicate rows into one

Merge duplicate rows in excel into one

Combine Rows 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
Free 15-day trial version, 14.68 MB

Select an Excel table

Follow the steps below to merge duplicated rows in Excel.

  1. Open the workbook with the table where you need to combine duplicate rows into one record. Then open Combine Rows Wizard by clicking on its icon.
    Combine Rows Wizard icon
  2. You can select your table by highlighting it or by typing its address in the Select your table field manually.
    Type the address of your table in this field
    Note. If you have standard auto filter option switched on, the add-in will merge only visible filtered duplicate rows in Excel. If you need to work with the entire table, turn the filter off before you run the add-in. If you want to work with some part of the table only, apply auto filter to it and then only the visible rows of your table will be processed and merged.
  3. Click the Next button to combine duplicate records in Excel. Click the Next button on the dialog box to continue

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.

  1. Tick the checkboxes next to the columns with the duplicated values. Tick the checkboxes next to key columns
  2. If you have a lot of columns in your table, take advantage of the Select All checkbox and all the checkboxes in the table with the list of columns will be checked. Then you can deselect those you don't need to match. Click the Select All button to check all columns
  3. It is also possible to indicate if there are header rows in your table. As a rule, the add-in indicates headers automatically. If it doesn't, you can check My table has headers. You can also uncheck this option if you want to include the first row.

    Pick this option to indicate that your table has headers
  4. On this step you can choose to ignore empty cells. Select the Skip empty cells option and the add-in will not take them into consideration. Select the Ignore empty cells option and the add-in will not merge them
  5. Click on the Next button to merge duplicate rows in your Excel worksheet into one.

Select columns to merge

On this step you choose one or more columns with duplicate values in Excel to combine into one record. You will see a list of columns in the table that were not selected as key columns.

  1. If you have a really big table with numerous columns, you can benefit from the Select All checkbox. Use the Select All and Unselect All buttons with big tables
  2. Tick the checkbox(es) next to the column(s) with the data you need to merge. Check the columns you need to merge
  3. Select a delimiter that will separate the merged values from the Choose delimiter drop-down list. You can either enter your own delimiter or select it from the predefined options.
    Choose a common delimiter
  4. When you select a column in the list, you can see an arrow nextto the name of the column in the Delimiter If you need to choose a different delimiter for certain value, click on the arrow and pick the delimiter you need to separate the joined values from the drop-down list. You can choose among semicolon, comma, space or line break. It is also possible to enter any delimiter of your choice. To do it, click in the Delimiter field next to the column name and type the separator you need. Choose a delimiter from the drop-down list
  5. On this step you can choose to remove duplicates or skip empty cells.
    • Tick the Delete duplicate valuescheckbox if the columns with the matching values you need to combine into one record may contain identical items.
    • Select the Skip empty cellsoption to avoid merging empty cells which can further result in extra blanks in your spreadsheet.
    Choose to delete duplicates and skip empty cells
  6. Then click the Finishbutton and see that all Excel duplicate rows are perfectly merged.

The Combine Rows Wizard joined 32 duplicate rows in under 1 minute!

Featured customers

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

Please remember that we work from 8am to 4pm GMT, this is 4:00am to 12:00pm EST,
1:00am to 9:00am PST, and our public holidays may differ from yours.

Ultimate Suite for Excel Professionals
 
 
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