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.
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 Extensions 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 in Google Sheets:
Click Next to proceed to the last step.
Remove Duplicates lets you choose one of 6 ways to deal with the found records in Google Sheets:
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 mouse clicks drastically.
Once the data is processed and you get the final message, you will have an opportunity to save the options the add-on has just used into a new scenario. In the future, run this scenario and it will initiate the search with the same settings right away.
You can also make the scenario run and find duplicates automatically at a certain time by itself.
Isn't this marvellous? 🙂
To save the 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:
It is also unavailable when you connect your scenario to a certain file since the tool can handle it even when it's closed. And in that case, there's no selected sheet.
When your scenario is ready, hit Save.
To start the scenario, go to Extensions > Remove Duplicates > Scenarios, find the necessary name and click Start.
This will initiate the work of the Remove Duplicates 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 Extensions > Remove Duplicates > Scenarios, pick the scenario you want to review, and click Edit:
You will see the same window with the scenario summary and will be able to rename it, change the spreadsheet, sheet & range, and turn its autostart on & off:
Press Save to keep the changes or Delete to get rid of the scenario permanently.
When you create a scenario, you can make it run automatically once or twice a day. You don't have to keep the required spreadsheets open or your computer turned on. The scenario will be executed on schedule on the Google servers even when you're offline.
To make Remove Duplicates run scenarios automatically, tick off the Run on schedule checkbox:
Then scroll the scenario outline down. You'll see all the related settings available under the Schedule autostart section:
You'll immediately see what time frames are already taken (either by the 1st run of the same scenario or by any other scenario):
With this setting on, a new sheet called Scenarios schedule log will be added to your file. It'll show you when each scenario was executed, whether it finished successfully (and what's the result), and when the next start is. If the scenario fails, you'll see a short description of what happened so you could fix the problem or email us for help.
When you schedule the scenario, hit Save.
You can see & easily manage schedules for all your scenarios from one place.
Go to Extensions > Remove Duplicates > Schedule:
The summary timetable will appear featuring all your scenarios. You'll see which ones are already on the schedule and which are not:
Hit Save to apply the changes, Cancel to go back to the summary timetable without saving the latest edits, or Delete to completely remove this scenario from your saved ones.
Run will call for the add-on and initiate this scenario right away.