How to combine duplicate rows, merge values, and add subtotals in Google Sheets

Whether you're new to Google Sheets add-ons or you already enjoy every bit of Power Tools, you'll love the news: you can take more tasks off your shoulders now that it has Combine Rows wizard.

When your Google spreadsheet has duplicates, there are several tools that let you handle them in any way you choose. But what if there is important unique information besides duplicate values on some of the rows? How often do you get tables where you need to combine related rows to one without losing adjacent details:

Bring duplicates to one row

You can't simply remove repeating records, you need to bring duplicates to one row while merging uniques and summing values with the same name. When you look at such data, you know there should be a simple way to join them. Well, now there is one. With Power Tools, all it takes is to open the Data group, click Combine Rows, and follow 3 steps:

  1. Select your data
  2. Select key columns with duplicates
  3. Choose how to bring uniques to one row: calculate the numbers or merge values that refer to the same record

That's it! Let me show you how it works.

Bring duplicates to one row in your Google spreadsheet

Once you start the add-on, it automatically selects the entire range with data in your sheet. This step is as simple as it can be: you can easily switch to another sheet, select or enter a different range, and follow to the next step where you specify the key column with duplicates:

Merge duplicates in your spreadsheet to one row

If the duplicate in your table involves several columns, for example, "First name" and "Last name", select both and the add-on will make sure it finds the same people:

Merge rows based on any combination of key columns

It will check any combination of key columns you have. You may want to find rows that have identical company names, city, and country, you have all that flexibility. There is also no need to sort your table, it will find duplicates in any case.

Combine duplicate rows and add subtotals in Google Sheets

What makes this tool especially useful is the possibility to process both: text and numeric values. When you select a column on the last step, choose the action you want to apply: merge values or calculate numbers. Pick the second one for the numbers in your spreadsheet and get a conditional sum across the range.

Well, not only that, all the standard functions are there to choose from: get the average or minimum amount, count or sum values of related entries in your Google sheet. Even COUNTA is there to show how many non-numeric values the key items had. For example, when you get replies from Google Forms in a spreadsheet, this lets you see the number of entries users made.

Merge duplicate rows and sum values in Google Sheets

When you select a column, be sure all your records will be preserved. Whether you have several rows with a person's form replies, or different product names referring to the same order, just select the column with the merge values action and pick a delimiter. Combine Rows will take the entries to one cell and separate them using the delimiters you enter or select on the last step:

Merge uniques and add subtotals in Google Sheets

You can also notice a quick solution to get only unique values in the resulting cells: select the option at the top that says Delete duplicate values and it will disregard any repetitions.

Whenever you want to group duplicate rows with important data in your spreadsheet, remember there is a simple and quick way to merge and sum up all related values. If you don't have the add-on, feel free to use this direct link to get it for your Google Sheets. We welcome any feedback or questions you have here or in our Facebook!

See also

5 Responses to "How to combine duplicate rows, merge values, and add subtotals in Google Sheets"

  1. Inessa says:

    Thank you so much for the article! It really helped.

  2. david orozco gudino says:

    2019 and still helps, maybe if you update it with the latest version of tha addon it would give you a SEO boost.

    Great advice.

  3. Lindsay says:

    This is an amazing tool. Thank you!
    Is there away to bring the newly calculated data to a new sheet. This would allow for the original data sheet to stay un-touched.
    Thoughts?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!