by 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 Duplicate Rows tool.
When your Google spreadsheet has duplicates, there are several tools that let you handle them in any way you choose. For instance, highlight them.
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:
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 Dedupe & Compare group, click Combine Duplicate Rows, and follow 3 steps:
- Select your data
- Select key columns with duplicates
- 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:
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:
It will check any combination of key columns you have. You may want to combine duplicates in Google Sheets 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.
Tip. Alternatively, you can split one sheet to multiple sheets so there's a table with the data per each company name, city, country, etc. Find all the details in this tutorial.
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 Sheets. 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.
Bring related values together
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 Duplicate Rows for Google Sheets will take the entries to one cell and separate them using the delimiters you enter or select on the last step:
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.
Automate the process using scenarios
All these steps can be avoided in the future by simply saving them into scenarios. Click Save scenario under your resulting message and you'll see its outline. You'll be able to name it and pick a sheet and a range that will be processed by this scenario:
When you run this scenario from the Google Sheets menu, it combines duplicates in the Google sheet you specified in that scenario right away.
Of course, you can always edit the sheet and the range from the same menu:
Video: Combine Duplicate Rows add-on for Google Sheets
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. Since a picture is worth a thousand words, here's a short video that will show everything you need to know about the add-on:
If you don't have the Combine Duplicate Rows yet, 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!
7 comments
Thank you for the tool. I'm wondering, is there a way for this process to be automatic? For example, if the spreadsheet gets constantly updated, is there a way for the tool to automatically merge the data each time?
Thank you for your Johnson!
I'm afraid it's currently impossible to process data with the tool automatically. You need to run it each time you want to combine duplicate rows.
However, the add-on features scenarios: you can save the settings you use most often and give this set a name – this is your scenario. Once you run it, your sheet will be processed right away, and you won't need to go over the same steps again. You can view, edit, and delete scenarios quickly anytime.
Feel free to check the detailed instructions on how to work with scenarios on the following help page:
https://www.ablebits.com/docs/google-sheets-combine-duplicate-rows/#use-scenarios
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?
Thank you for your feedback, Lindsay.
There's a special option to Create a backup copy of the sheet on the very first step of the tool. Simply tick it off and you'll have two sheets: one with the calculated data and another with your original table intact.
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.
Thank you so much for the article! It really helped.
Thank you for your feedback, Inessa!