Combine duplicate rows in Google Sheets

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.

Start Combine Duplicate Rows

Go to Extensions > Remove Duplicates > Combine duplicate rows to run the add-on:

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:

Step 1: Select your data

Here you are to adjust the range where you want to combine 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:

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.

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:

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:

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.
     

     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:
     

   • Choose to Calculate numbers and select the function to apply when you want to subtotal the numbers that refer to the same record:
     
     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:

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:

You will see a short summary of all options you have used just now — this is your scenario preview:

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:
   
   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.

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:

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:

You will see the entire scenario outline again:

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.