Use the Remove Duplicates add-on for Google Sheets to deal with duplicated data in your table. Find, delete, move, copy, or clear duplicate or unique rows in your spreadsheet.
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.
Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 5 million cells.
If you're familiar with Remove Duplicates, try out Quick Dedupe to find repeats in just one step.
If your task is to compare two sheets or tables for duplicates or uniques, take advantage of the Compare columns or sheets tool instead.
To look for dupes or uniques in all single cells all over an entire spreadsheet, Find duplicate or unique cells is what you should use.
To merge or consolidate unique records that refer to multiple duplicate values on different rows, turn to Combine Duplicate Rows.
Open the Add-ons menu, find Remove Duplicates, and click on the Find duplicate or unique rows tool:
Here you can select the range with the data you want to search in:
By default, the add-on highlights the entire table in the sheet of your choice.
You can alter the range by entering the needed address in the Select the range with your data field or by clicking the Select range icon. The latter will open a dialog box and allow you to pick the necessary cells:
You can choose to back up your original table by selecting the checkbox Create a backup copy of the sheet.
Click Next to proceed to the second step.
The add-on lets you check your table for 4 types of data:
You can return to step 1 by clicking Back.
Click Next to go on to the next step.
Here you will see a list of columns in your table — tick off those you want to check for duplicates:
Click Next to proceed to the last step.
The add-on allows you to choose one of six ways to deal with the found records:
Select the radio button next to the action you'd like to apply:
Click Finish to find duplicate or unique values.
Once the add-on completes the search, you will see the summary of the results with the number of found values and the action that was applied:
To save these settings and use them in the future, click Save scenario.
If you work with the add-on often, most likely you have to deal with going over the steps and choosing the same options over and over again. Scenarios will help you reduce the number of your mouse-clicks drastically.
Once the data is processed and you get the final message, save the options the add-on has used just now into a new scenario. In the future, run this scenario and it will initiate the search with the same settings right away.
Isn't this marvellous? 🙂
To save settings into a scenario, click the corresponding option under the result message:
You will see the window with the summary of settings. Look through it to make some adjustments for the future scenario if necessary:
When your scenario is ready, hit Save.
To start the scenario, go to Add-ons > Remove Duplicates > Run scenario and click on the necessary name:
This will initiate the work of the add-on with all the necessary settings.
Once it's finished, you will see the result message saying what scenario has been used and what has been found:
You can view and manage your scenarios anytime from the Google Sheets menu.
Just go to Add-ons > Remove Duplicates > View & manage scenario and click the name of what you want to review:
You will see the same window with the scenario summary and will be able to rename it and change the sheet or the range:
Press Save to keep the changes or Delete to get rid of the scenario permanently.
Responses
Hello - I want to buy this.... but what I need to do is to find duplicate words in a column in Google Sheets and export those found duplicate words to another Google sheet without removing them from the original. How do I do this and will this tool do that? Hope so 🙂
Thanks very much,
Garry
Hi, Garry,
Thank you for your interest in our product!
If I understand your task correctly, our Find Duplicate Cells tool from Remove Duplicates will help you better:
https://www.ablebits.com/docs/howto-find-duplicate-cells/
You'll be able to select your column as a range to search in, and then copy the found dupes to another sheet/spreadsheet.
You can test Remove Duplicates for free for 30 days to make sure it works as you need.
You can also share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task. We'll check how your data is stored and see if our software can help.
How can I select duplicates across three [3] or more columns?
I am using "remove duplicates" to select duplicates across two columns and copy them to another column.
I am trying to do the same function but across 3 columns.
Does anybody know how to do this?
Thank you for your help!
Hi Alex,
Thank you for using our product and for your comment.
If I understood you correctly, you need to compare three or more columns and select the values that are found in all the columns. For such a task better use the Compare Columns or Sheets tool.
Unfortunately, there is no way to compare more than two tables at once. Currently, there is only one option that you can use: compare two columns and copy duplicates, and then compare the result with the third column.
There is a workaround for your case, however. You can copy all columns into a single sheet and use the Find Duplicate or Unique Cells tool.
i paid for the whole year and its charging me again.
Hello, Megan,
Thank you for contacting us.
I'm really sorry you've encountered this problem with our add-on.
Could you please email us to support@ablebits.com with your order ID and the email address you registered the add-on to?
We'll look into the issue.
Thank you.
Is there any way of setting the app so that I will run once each day or each hour?
thank you,
Brian
Thank you for your question, Brian.
Â
I'm afraid it's technically impossible to run our add-ons automatically at the moment.
However, since this is a common request, we are already working on a solution that will cut down the number of clicks on the same tasks.Â
I can't tell you the exact timing yet but I can contact you back when the functionality is supported.
Â
Please let me know if you have any other question.
Brian,
I'm happy to let you know that recently we've introduced scenarios to our add-on. Now you can automate repetitive tasks and check for duplicates in a click.
Just 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 in this part of the article:
https://www.ablebits.com/docs/howto-find-duplicate-rows/#use-scenarios
It's a pilot feature, so your feedback is eagerly anticipated and much appreciated. 🙂
If you have any question, please contact us at support@ablebits.com.
Hi, moths ago we paid for a lifetime license and were prompted to pay again today. Because we didn't have time to investigate the issue, we paid for another lifetime license in order to get up and running as quickly as possible. However, even after purchasing this second one, its still prompting us to purchase a lifetime license. As of now, we've paid for your service twice and still don't have access
Hi, Devin,
Thank you for contacting us. I'm really sorry you're having this difficulty with our add-on.
I'll reply to you with all the details via email since the message will contain some personal info about your licenses.
Please check your Inbox for a message from Ablebits support team.
Thank you for your understanding.
How do you hide formulas in cells on shared google sheet without using importrange?
Hi Esther,
Thank you for the question.
For us to be able to assist you, please clarify, what do you mean by saying 'hide formulas'? Do you want to make them invisible? Or the task is to prevent other users from editing the formulas? Or maybe something else?
Thank you.
To prevent others from seeing the formulas.
Hi Esther,
Unfortunately, there is no way to hide formulas in Google Sheets. However, you can try some of the techniques described here.
Hello!
If I buy license, I have the follow questions.
How many users can use the key?
How many google sheets docs can use the key per a person?
If I access for edit other google sheets (not owner). Can I add the software and use my key?
Hello Yuriy,
Thank you for your questions.
Our subscription is account-based - one key can be used under one Google account at a time.
If the product is going to be used under different accounts at the same time, you need to have several subscriptions, so that there is one for each account.
You can use the add-on in both your spreadsheets and shared ones.
You will find these and other answers regarding our subscriptions on these pages:
Licensing: https://www.ablebits.com/docs/gsuite-add-ons-licensing/
Purchasing: https://www.ablebits.com/docs/gsuite-add-ons-purchasing/
Hi, i am user of remove duplicates, and have 2 accounts in google, can i use in both or get a discount for 2nd account?
Hello Pablo,
Thank you for your questions.
Ablebits subscription is account-based, meaning it can be activated under one Google account at a time. If you have several accounts, you need to acquire several subscriptions so there's one for each account.
If you purchase 2 subscriptions at a time, you automatically get a 20% volume discount. You can always make the purchase right from the add-on.
Please let us know if you have other questions or need any assistance.
Hello,
In June I purchase a 1 year subscription, and it's prompting me to enter my license number again. I can't seem to find my license number anywhere, and there doesn't seem to be a way to login to retrieve it.
Hello Theresa,
Since the reply contains your license details, we have just replied to you by email. Please check your Spam/Junk/Trash folders if you don't see the message in your Inbox.
After duplicates are highlighted, how do you un-highlight them without the "undo" function?
Thank you.
Hello Patrick,
Thank you for your question.
Please note that we always recommend creating a back-up copy of your original table by selecting the corresponding checkbox on step 1 of the add-on before processing your data. If this option doesn't suit you, then you can try to use the standard Fill color feature in Google Sheets to change the background of the highlighted rows or use the spreadsheet Version history feature (File -> Version history -> See version history) instead of the Undo function. If you have any other questions, please do not hesitate to ask.
Hello,
Is there any way to delete duplicates from several Google Sheets at once?
Hello Mario,
Thank you for your interest in our product. I'm afraid it's currently impossible to process multiple sheets at a time. You need to run the add-on in each sheet you want to remove duplicates from.
However, scenarios are available for our Remove Duplicate Rows. 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'll only need to change the ranges and sheets to process as the scenario gets saved for the particular set of data.
If this workaround seems interesting to you, feel free to check the detailed instructions on how to work with scenarios over here:
https://www.ablebits.com/docs/howto-find-duplicate-rows/#use-scenarios
Alternatively, there is a Quick Dedupe tool that removes your duplicates in just one step, check it out: https://www.ablebits.com/docs/google-sheets-find-duplicates/