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

Go to Extensions > Remove Duplicates > Combine duplicate rows to run the add-on:
Combine Duplicate Rows in Remove Duplicates.

Tip. The tool is also part of the Power Tools add-on. You'll find its icon in both Merge & Combine and Dedupe & Compare groups:
Combine Duplicate Rows icon in Power Tools.

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 the used range — cells with data till the blank row & column — 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 display 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 the used range 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 Extensions > Remove Duplicates > Scenarios, select the required scenario and click Start.

Note. If you don't see Scenarios in the menu, just click Combine duplicate rows to open the add-on itself once. This is necessary for new spreadsheets and those where the add-on hasn't been run yet. Your scenario (and all future ones) will then appear in the menu automatically.

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 Extensions > 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

Kayden Break says:
May 10, 2023 at 5:10 am

just curious whether possible to do it like this,
I have a set of data example
Column A1-A4 (same things) B1 - B4 (unique)
possible to do merge duplication to ensure that I will still have A1-A4 = 1 cell and B1-B4 remain un-change?
Because as per above function it will be A1-A4 all merge to A1 and B1-B4 all merge into B1.

Hello Kayden,

Thank you for the question.

We can offer you two ways how to solve your task with our add-ons. You can first use the Merge Values tool to combine duplicate values and then use the Split Text tool for B1-B4 cells.

Or try the Remove Duplicate Cells tool for the whole range and in Step 3 opt for the Clear values option.

Should you need any further assistance from our side, please email us at support@ablebits.com

Quinn Bilodeau says:
November 9, 2022 at 4:31 am

"Combine duplicate rows" is an amazing tool!

I am wondering if it is possible to see the Google Sheets formula that this tool creates/executes (assuming it does create a Google Sheets formula), much like the "Multiple VLOOKUP Matches" tool displays the query function it creates.

My end goal would be to assign several "Combine duplicate rows" Scenarios ( or formulas) to a drop-down list in a single cell and run different "reports" or "views" of a master data table. The different reports from the dropdown list would select a specific duplicate column "key" and the accompanying combination of columns to merge for the specific view/report of the master data table.

Would this be possible? Does the "Combine duplicate rows" tool create a complex query formula, or does it run at a deeper programming level?

Keep creating epic Google Sheets extensions!!

Hello Quinn,

Thank you for your comment. Sorry, the add-on doesn't create a formula at the moment. However, it is an interesting idea, I've forwarded it to our developers.
Currently, you can use scenarios instead. Unfortunately, they can't be called via macros or other formulas.

If it's not too much trouble for you, we would appreciate it if you rate our add-on and write a few words on Google Workspace Marketplace. Your positive feedback motivates us to provide the best experience for you and helps others understand how our product makes their life easier :)

Thank you in advance!

This is almost working perfectly for me, it appears to be exactly what I need - but its only working for the first 75 rows. Am I doing something wrong or is this a limitation of the free trial?

Hello Jack,

Thank you for your feedback.

Please note that the trial version of the add-on is fully functional and doesn't have any limitations. However, there is a limit for 5 million cells in Google Sheets. If it is not your case, then please send us screenshots of the options you choose on each step of the add-on to support@ablebits.com and share an editable copy of your spreadsheet with support@apps4gs.com.

Note. We keep support@apps4gs.com for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by sending an email to support@ablebits.com.

We'll try to reproduce the issue on our side and find its cause. Thank you.

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

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

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.

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!

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.

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.

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.

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.