Combine duplicate rows in Google Sheets

When the same records take several lines, the Combine Rows add-on from Power Tools 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.

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 Rows tool

Start Combine Rows

  1. Start Power Tools add-on, Merge & Combine group, in the Google Sheets menu:
    Merge & Combine in Power Tools.
  2. Click on the Combine rows icon:
    Combine 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 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 Mark and last name Twain.
    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.

      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 (Semicolon, Line break, 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 number 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.

Responses

Great tool, helps me a lot

Reply

Thank you for your lovely feedback, Irena! 🙂

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

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.