Combine duplicate rows in Google Sheets

When the same records take several lines, the Combine Duplicate Rows add-on is your best assistant for bringing all relevant information together. This page shows how to combine rows with duplicate entries that have unique information in adjacent columns in 3 simple steps.
Combine duplicate rows in Google Sheets.

Video: How to combine duplicate rows in Google Sheets

Before you start

Formulas

The add-on processes values only. There is no technical possibility for it to keep your formulas when it combines data from several rows into one.

Merged cells

The add-on doesn't process merged cells. If there are any in the rows you want to combine, they will be unmerged, and only the value from the top-left cell will be kept.

Backup copies

We care about your data and suggest you always create backup copies of your spreadsheets. A special option of the add-on will do that for you if you select it.

How to use the Combine Duplicate Rows tool

Start Combine Duplicate Rows

  1. Start Power Tools in the Google Sheets menu — Add-ons > Power Tools > Start:
    Merge & Combine in Power Tools.
  2. Open the Merge & Combine (or Dedupe & Compare) group and click the Combine duplicate rows icon:
    Combine Duplicate Rows icon in Power Tools.
Tip. The tool is also part of the Remove Duplicates add-on — Add-ons > Remove Duplicates > Combine duplicate rows:
Combine Duplicate Rows in Remove Duplicates.

Step 1: Select your data

Here you are to adjust the range where you want to combine duplicated rows:
Pick the range with the data that contains duplicated rows.
The add-on picks your entire used range by default.

To modify it, either enter the changes right in the field or click on the Select range icon. The latter will bring up a dialog window that will automatically get any range you choose manually. Select the necessary cells and click OK to confirm the reference:
Use a special pop up window to select the range directly from the spreadsheet.
To quickly highlight all data back, use the Auto select button.

We always recommend ticking off the option to Create a backup copy of the sheet. It will duplicate the original spreadsheet before its records are combined.

Note. Make the copy of your sheet especially if there are any formulas in the range.

Click Next to continue to step 2.

Step 2: Identify key columns

This step lets you define the columns with duplicate key values that you want to combine:
Pick the columns with duplicate key values.

  1. You will see three additional options here:
    • Data has header row. If you have a title row in the selection, choose this option to see column names and leave the top row intact.
    • Skip empty cells. If there are blanks in your key column, you can leave them as they are. Otherwise, the add-on will join them into one row as well.
    • Match case. Select this option if you want to consider such records as A1 and a1 as different. Leave this option unchecked to ignore the case of the key values and combine A1 and a1 into one row.
  2. To use many matching columns, use the checkbox at the top of the table to tick off them all at once.
    Note. Make sure to leave out the columns with the unique entries to merge. You will specify how to combine the values in adjacent columns on step 3.
  3. Choose columns with the key values to combine from the list. It can be one column, e.g. a column with IDs, or a combination of columns like first and last names. In our example, it will bring together only those rows that have the same first name J.K. and last name Rowling.
    Note. The add-on will look for matches in all the columns of interest at once. It doesn't matter if the records are not directly one under the other, the tool will combine them anyway, so you can keep your current sorting order.

Once you specify key columns on this step, click Next to follow to the last step.

Or choose Back to change the range with data.

Step 3: Choose columns with the values to merge

The last step lets you identify the values to merge in your selection along with the way to do it:
Choose the columns with the values to sum.

  1. There are three additional options that refer to the merge process:
    • Delete duplicate values. This will let you keep only unique records.

      For example, if you are combining rows by order IDs and merging names, one ID may take several rows with the same name. You can keep the first occurrence of the name in the resulting row and avoid repetitions.
      Delete duplicate values.

      Note. This option does not apply to columns with numbers you choose to calculate.
    • Skip empty cells. Select this option to avoid adding extra delimiters for empty cells. Disregard this if it's important for you to see the blanks.
    • If all your columns contain data of the same type, use the Synchronize action option to match the way of merging them. Once you set up the way to merge records in one column, it will be auto-set for all other selected columns.
  2. Use the top checkbox to select all columns.
    Note. For the columns you don't select here, not only won't their values be merged, but also only the first record for each key value will remain. Other records will be lost.
    Tip. Combine this option with Synchronize action to set the way to merge for all columns at once.
  3. For each column of interest, use the Action and Delimiter / function areas to decide on the way to process the data:
    • Select Merge values if you want to list all entries that refer to the same record.

      Pick one of standard delimiters (Line break, Semicolon, Comma, Space) from the drop-down list or enter any custom separators in the same field. For example, you can bring all book titles to the same cell and use a line break to separate them within a cell:
      Pick the necessary delimiter to merge values.

    • Choose to Calculate numbers and select the function to apply when you want to subtotal the numbers that refer to the same record:
      Select the function to calculate numbers.
      Tip. Even though the name of the action implies working with numbers, you can use the COUNTA function for text values, e.g. count the number of ordered products instead of listing their names.

Get the result

Once you set up the action for all columns with the values you'd like to keep, click Finish and see the summary of the merged rows in the add-on window:
See the result with the number of processed and merged values.

Tip. Google Sheets allows you to undo the changes, and you can always revert to the auto-created backup copy if you wish to modify the process.

How to work with scenarios

If Combine Duplicate Rows is your go-to add-on, chances are you run it often and go through the same options over again. To help you automate the process a bit, we implemented scenarios for this tool.

What is a scenario

A scenario is a set of all options that you choose on each step of the add-on. You can save these settings to run later either on the same table with new data or on another table of the same structure.

Save the scenario

When the add-on finishes combining duplicate rows and shows you the result message, click Save scenario:
Click Save scenario to save a set of settings.
You will see a short summary of all options you have used just now — this is your scenario preview:

See the scenario outline.

  1. Name your scenario so you could understand what it does and quickly find it among other scenarios.
  2. Select the sheet that you want to alter with this scenario:
    • To process a sheet where your mouse cursor stands when you run the scenario, choose [Selected sheet].
    • Or pick any particular sheet to always handle it no matter where you stand in the spreadsheet when you start the scenario.
  3. Set the range that you want to alter with the scenario:
    • Choose [Selected range] and the scenario will be applied to cells that are selected at the moment.
    • Pick [All data] to have the add-on detect the entire used range.
    • Or specify a particular range to process.
  4. Look through the options you're about to save to make sure everything is correct:
    Review the options you're about to save as your scenario.

    Note. These settings cannot be modified here. If you want to adjust some of them, you will need to restart the add-on and fine-tune the options while going through the steps.

If everything looks good, click Save.

Run your scenario

To start the scenario, go to Add-ons > Remove Duplicates > Scenarios, select the required scenario and click Start:
Start the required scenario.
Combine Duplicate Rows will begin to work with the data according to the scenario settings.

Once it's done, you will get the result message saying what scenario has just worked and what it's processed:
Result for the 'Books per author' scenario.

Manage scenarios

To view the scenario or to change the sheet and the range you want to alter, go to the same Add-ons > Remove Duplicates > Scenarios menu, pick the scenario and select Edit this time:
Edit the scenario.
You will see the entire scenario outline again:
Scenario outline.
You can give it a new name and select other sheet & range.

If you make any changes, press Save to keep them. Click Run to start the scenario right away, or hit Delete to remove it completely.

Responses

I need information on how google sheets work? I been having such a rough time with this my issue when my team capture information on google sheets and the sheets are combined for some reason the information that is capture from my team changes once the spreadsheets are combined. I am trying to make sense of this because it causing so much do over at work. I really need someone to help me figure this out. This is an example on what's going so say for instint one of team members capture data onto google sheets, and the data is barodes, box# and on the spreadsheets and its about 1000 per team member so the 1st order was 14,000 and my team was told that it was 800 duplicate out of that 14,000 but its impossible because each team member has there own labels and numbers don't repeat and they sit at least 10 - 20 feet apart.. PLEASE PLEASE PLEASE HELP ME SLOVE THIS ISSUE

Reply

Hello Sheka,

Thank you for contacting us. We are always ready to help, please share a small sample workbook with 2 sheets: 1 - your source data and 2 - the result you expect to get with support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.
To grant us access to your data, press the Share button at the upper right corner of Google Sheets and enter support@apps4gs.com. Thank you.

Reply

Hi,
Is there any way for me to have the results of combining my data go to a master data sheet and not into my raw data sheet?
Thanks, Ramy

Reply

Hello Ramy,

Thank you for your question.

Please note that there is no option to choose the location where to put the results in the current version of the Combine Duplicate Rows tool. However, you can tick off the Create a backup copy of the sheet option on step 1 of the Wizard and get 2 sheets after the add-on processes your data: the one with your original data and the other one with the combined data. Hope this will work for you.

Reply

Hi,

First of all, great tool! I have a question, however, when merging duplicate rows, some have empty cells and I would like it to grab the value from the duplicate instead of leaving it blank. Is there a way to do that?

Thanks in advance!

Reply

Hi Irvin,

Thank you very much for your feedback. I'm sorry but it is not entirely clear what result you expect to get. For us to be able to help you better, please share a small sample workbook with 2 sheets: 1 - your source data and 2 - the result you expect to get with support@apps4gs.com. Just about 10-20 rows will be enough.

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

Reply
Maxine Bremner says:
June 18, 2020 at 7:11 am

This was incredibly helpful - very simple to follow the steps! Thank you so much for putting this together.

Reply

Thank you for your comment, Maxine.

Glad to hear that you have found the instructions for our tool helpful.

Reply
Kai O'Neill says:
April 15, 2019 at 6:53 pm

The finish button is disabled and no actions I have tried make it clickable.

Reply

Thank you for reporting a problem to us, Kai.

For us to be able to help, please email us to support@ablebits.com with the screenshots of each step and the options you select there.
Also, when the button is disabled, press F12 on your keyboard, go to the Console tab, copy the last 15-20 lines and send them to us.

All this info will help us understand the cause of the problem. Thank you.

Reply

I am not able to see combine row function under Ablebits data tab in excel. Where to find it

Reply

Thank you for your question, Hiral,
I'm sorry but the add-on described here is for Google Sheets.

For Excel, this add-in is called Merge Duplicates. It's under the Ablebits Data tab, and you can find its help file on the page below:
https://www.ablebits.com/docs/howto-merge-data/

Reply

Great tool, helps me a lot

Reply

Thank you for your lovely feedback, Irena! 🙂

Reply

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

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.