by Natalia Sharashova, updated on
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:
That's it! Let me show you how it works.
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.
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.
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.
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:
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!
Table of contents
