How to combine duplicate rows in Excel 

Merge Duplicates Wizard 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

Pay attention to the Create a backup copy of the worksheet checkbox. We recommend keeping this option selected as Excel doesn't let you cancel changes made by add-ins.

Note that hidden rows are still processed by the add-in.

How to use Merge Duplicates

Start Merge Duplicates

On the Ablebits Data tab in the Merge group, click the Merge Duplicates icon:
Start Merge Duplicates for Excel.

Step 1: Select your table

On the first step, the add-in picks the entire range with your data:
Select your table to merge duplicates.

  • To specify a different range, click 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.
  • 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.

Do not forget to tick the Create a backup copy of the worksheet checkbox to have a copy of your data.

Click Next.

Step 2: Choose key columns with duplicate records

On this step, you can see a list of all columns you have in your Excel sheet:
Select key columns with duplicates to merge.
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, tick off the My table has headers checkbox at the top. 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.
  • Take advantage of the Select All button if all the columns in your table are the key ones. Use Unselect All if you picked the wrong columns.
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 the Next button.

Step 3: Pick columns with the values to merge

On this step, select the columns with the entries to merge:
Pick columns with the values to merge with the Merge Duplicates tool.
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:
    Merge duplicates and delete duplicate values.
  • Select the Skip empty cells option to avoid adding extra delimiters to blank cells.
  • 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, space, and a line break. Besides, you can select one of the following aggregate functions: AVERAGE, SUM, COUNT, MAX, MIN, PRODUCT, STDEV.S, or STDEV.P:
    Select delimiters for merged values. Choose aggregate functions.
  • Note. The tool uses the standard Excel functions (SUM, AVERAGE, COUNT, 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:
    Choose a different delimiter for each column.
  • The Select All and Unselect All buttons help you quickly pick all the columns or unselect them at once.
Tip. If you need to change your settings on the previous steps, click the Back button and make the necessary corrections.

Click Finish to get the duplicates in the selected columns merged:
Merge Duplicates Wizard for Excel

Responses

I am having an issue when I try to sum decimal numbers. When I merge the cell with decimal numbers it makes the formula looks like two numbers in a cell (ex. cells 2,4 and 3,4 appears as sum(2;4;3;4) )
Obs. I am from Brasil and here we use comma as separator of decimal numbers.

Reply

Hello, Raul,

Thank you very much for your comment. You helped us to find a bug and our developers are already working on fixing this issue. As soon as it is solved, I will send you details via e-mail.

Thank you!

Reply

Just find this amazing function, saved me for hours of work
OK, it didn't removed all duplicate value, some "," before and after was not removed, but all together saved me for lot's of work

Tom

Reply

Hi, Tom,

Thank you so much for your kind feedback, it is always very important to know that our products help people save precious time!
I am sorry to know that you’ve encountered difficulties with removing quotes. Please contact our support team with this issue at support@ablebits.com. It would be perfect if you could attach a sample of your dataset and indicate the version of Ultimate Suite you use. We’ll do our best to help you.

Thank you.

Reply

I can't chose delimiter [Line break]. How to chose it ?Pls help me !

Reply

Hello Thuc,

To choose the line break as a delimiter on the final step of the wizard, just select the following symbol from the "Choose delimiter" drop-down list: ↲

If there is anything else we can help you with, please let us know.

Reply
Milan Mehta says:
May 24, 2019 at 9:55 pm

Hi ,

I downloaded the ultimate suite, but am not able to find the "Combine Rows" functionality.

Reply

Hi Milan,
Thank you for contacting us.

Please note that Combine Rows was renamed to Merge Duplicates with a major upgrade we have released. You can find it in the "Merge" section under the "Ablebits Data" tab in the new version of Ultimate Suite.

Feel free to contact us again if you have any other questions or need further assistance.

Reply

Is there an option to not merge the values into a single cell, but rather to use a new cell (column) for subsequent values? I can always use a character that isn't in my data set and then use text to columns to achieve the result, but that assumes that one of the available delimiters isn't in use.

Reply

Hello Robert,
Thank you for contacting us.

I'm sorry, but your task is not entirely clear. For us to be able to help you better, please send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten the table to 10-20 rows.

We'll look into it and see if our software can help.

Reply

I am floored by how much is in this tool and am so excited to use this for a lot of excel projects!

I am trying to currently merge rows with duplicate data in Column H, but also override data from Columns B-G rather than put it in one cell with delimiters. Column A will be the determiner as to which data in B-G to override. For example, my first argument is to merge duplicate data identified in H, second argument is to override any data in B-G (unless null) if column A =NEW.

My long answer is probably use the delimiter, then run an if function to remove data from those cells...I was hoping there would be a simpler option. I hope this makes sense - I also emailed support but it is the weekend and I'm hoping for a quick answer.

Reply

Hello Jennifer,
Thank you for contacting us.

Our support team assistant has just replied to you via email. Please check your Inbox. If you have any questions, do not hesitate to address them to support@ablebits.com. Thank you.

Reply
Arsalan Qureshi says:
September 30, 2019 at 8:45 pm

Hey There,

I merged a table in excel with duplicate values, once I updated my source table with new values and hit refresh the duplicate values start showing up again.

Could you please help me with this issue.

Regards,

Arsalan Qureshi

Reply
Katerina Bespalaya says:
October 1, 2019 at 12:52 pm

Hello Arsalan,

For us to understand the problem better, please send a sample workbook with the duplicate values and the expected result to support@ablebits.com. Please also describe in detail what steps you follow before the issue occurs and include screenshots of each step of the Wizard with the selected options into your email.

We'll try to reproduce the issue on our side and see what is causing the problem. Thank you.

Reply

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-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