When the same records take several lines, the Combine Duplicate Rows add-on 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.
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.
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.
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.
Go to Extensions > Remove Duplicates > Combine duplicate rows to run the add-on:
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.
This step lets you define the columns with duplicate key values that you want to combine:
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.
The last step lets you identify the values to merge in your selection along with the way to do it:
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.
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:
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:
If Combine Duplicate Rows is your go-to add-on, chances are you run it often and go through the same options over again. To help you automate the process a bit, we implemented scenarios for this tool.
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.
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:
If everything looks good, click Save.
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:
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.
just curious whether possible to do it like this,
I have a set of data example
Column A1-A4 (same things) B1 - B4 (unique)
possible to do merge duplication to ensure that I will still have A1-A4 = 1 cell and B1-B4 remain un-change?
Because as per above function it will be A1-A4 all merge to A1 and B1-B4 all merge into B1.
Thank you for the question.
We can offer you two ways how to solve your task with our add-ons. You can first use the Merge Values tool to combine duplicate values and then use the Split Text tool for B1-B4 cells.
Or try the Remove Duplicate Cells tool for the whole range and in Step 3 opt for the Clear values option.
Should you need any further assistance from our side, please email us at email@example.com
"Combine duplicate rows" is an amazing tool!
I am wondering if it is possible to see the Google Sheets formula that this tool creates/executes (assuming it does create a Google Sheets formula), much like the "Multiple VLOOKUP Matches" tool displays the query function it creates.
My end goal would be to assign several "Combine duplicate rows" Scenarios ( or formulas) to a drop-down list in a single cell and run different "reports" or "views" of a master data table. The different reports from the dropdown list would select a specific duplicate column "key" and the accompanying combination of columns to merge for the specific view/report of the master data table.
Would this be possible? Does the "Combine duplicate rows" tool create a complex query formula, or does it run at a deeper programming level?
Keep creating epic Google Sheets extensions!!
Thank you for your comment. Sorry, the add-on doesn't create a formula at the moment. However, it is an interesting idea, I've forwarded it to our developers.
Currently, you can use scenarios instead. Unfortunately, they can't be called via macros or other formulas.
If it's not too much trouble for you, we would appreciate it if you rate our add-on and write a few words on Google Workspace Marketplace. Your positive feedback motivates us to provide the best experience for you and helps others understand how our product makes their life easier :)
Thank you in advance!
This is almost working perfectly for me, it appears to be exactly what I need - but its only working for the first 75 rows. Am I doing something wrong or is this a limitation of the free trial?
Thank you for your feedback.
Please note that the trial version of the add-on is fully functional and doesn't have any limitations. However, there is a limit for 5 million cells in Google Sheets. If it is not your case, then please send us screenshots of the options you choose on each step of the add-on to firstname.lastname@example.org and share an editable copy of your spreadsheet with email@example.com.
Note. We keep firstname.lastname@example.org for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by sending an email to email@example.com.
We'll try to reproduce the issue on our side and find its cause. Thank you.
I need information on how google sheets work? I been having such a rough time with this my issue when my team capture information on google sheets and the sheets are combined for some reason the information that is capture from my team changes once the spreadsheets are combined. I am trying to make sense of this because it causing so much do over at work. I really need someone to help me figure this out. This is an example on what's going so say for instint one of team members capture data onto google sheets, and the data is barodes, box# and on the spreadsheets and its about 1000 per team member so the 1st order was 14,000 and my team was told that it was 800 duplicate out of that 14,000 but its impossible because each team member has there own labels and numbers don't repeat and they sit at least 10 - 20 feet apart.. PLEASE PLEASE PLEASE HELP ME SLOVE THIS ISSUE
Thank you for contacting us. We are always ready to help, please share a small sample workbook with 2 sheets: 1 - your source data and 2 - the result you expect to get with firstname.lastname@example.org. I kindly ask you to shorten the tables to 10-20 rows.
To grant us access to your data, press the Share button at the upper right corner of Google Sheets and enter email@example.com. Thank you.
Is there any way for me to have the results of combining my data go to a master data sheet and not into my raw data sheet?
Thank you for your question.
Please note that there is no option to choose the location where to put the results in the current version of the Combine Duplicate Rows tool. However, you can tick off the Create a backup copy of the sheet option on step 1 of the Wizard and get 2 sheets after the add-on processes your data: the one with your original data and the other one with the combined data. Hope this will work for you.
First of all, great tool! I have a question, however, when merging duplicate rows, some have empty cells and I would like it to grab the value from the duplicate instead of leaving it blank. Is there a way to do that?
Thanks in advance!
Thank you very much for your feedback. I'm sorry but it is not entirely clear what result you expect to get. For us to be able to help you better, please share a small sample workbook with 2 sheets: 1 - your source data and 2 - the result you expect to get with firstname.lastname@example.org. Just about 10-20 rows will be enough.
We'll look into your task and see if our software can help.
This was incredibly helpful - very simple to follow the steps! Thank you so much for putting this together.
Thank you for your comment, Maxine.
Glad to hear that you have found the instructions for our tool helpful.
The finish button is disabled and no actions I have tried make it clickable.
Thank you for reporting a problem to us, Kai.
For us to be able to help, please email us to email@example.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.
I am not able to see combine row function under Ablebits data tab in excel. Where to find it
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:
Great tool, helps me a lot
Thank you for your lovely feedback, Irena! :)
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!