Find duplicate or unique rows in one Google sheet

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.

• Video: How to remove duplicates in Google Sheets
• Before you start
• How to use the Remove Duplicates add-on
  • Start Remove Duplicates
  • Step 1: Select your data
  • Step 2: Choose what to find
  • Step 3: Pick the columns to check
  • Step 4: Decide what to do with the found values
  • See the result
• How to work with scenarios
  • Video: Remove Duplicates for Google Sheets — Automation
  • What is a scenario
  • Save the scenario
  • Run your scenario
  • Manage scenarios
• Run scenarios on schedule
  • How to schedule your scenario
  • Manage scenarios timetable

Before you start

• Only complete matches are treated as duplicates. Partial matches are not considered duplicates. And keep an eye out for excess spaces: they may interfere with results as well. Our Remove tool will help you get rid of them.
• 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 10 million cells.

How to use the Remove Duplicates add-on

Start Remove Duplicates

Open the Extensions menu, find Remove Duplicates, and click on the Find duplicate or unique rows tool:

Tip. You will also find Remove Duplicate Rows in the Dedupe & Compare group of Power Tools:

Step 1: Select your data

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.

Step 2: Choose what to find

The add-on lets you check your table for 4 types of data:

• Pick the 1st one to delete duplicate rows in your Google Sheets table. The add-on will keep unique records and the first entries of any repeated records you have. Your dataset will become clear of any extra replicates.
  Note. Only complete matches are treated as duplicates. Partial matches are not considered duplicates.

  

• Select Duplicates + 1st occurrences to identify the original entries as well as their copies:
  
• Choose the Uniques option to find only those rows that don't have any duplicates in the sheet:
  
• The Uniques + 1st occurrences option lets you find everything except for the duplicates. The add-on will look for the first entries of duplicated data in addition to unique records:
  

You can return to step 1 by clicking Back.

Click Next to go on to the next step.

Step 3: Pick the columns to check

Here you will see a list of columns in your table — tick off those you want to check for duplicates in Google Sheets:

Note. If you check more than one column, a row will be considered duplicate if the values in all the selected columns are the same:

• You can tick off the Skip empty cells option to ignore duplicated blanks in the selected columns. Otherwise, blank matches will be considered duplicates.
• If you have header rows, make sure to check My table has headers to see your column titles. If you don't have headers, unselect this box to display column names.
  Tip. Take advantage of the 1st row content fields to see what kind of data you have in each column.
• Use the Match case option to treat the records that differ only in the case as uniques.

Click Next to proceed to the last step.

Step 4: Decide what to do with the found values

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:

• Highlight duplicate rows or other found entries by picking the Fill with color option. Click on the arrow next to it to pick the hue you'd like to use.
• Choose to Add a status column to identify the rows of interest in a separate column.
  
  Tip. You can make use of this additional column later to sort the data.
• The Copy to another location option lets you review search results in another sheet, spreadsheet, or a range of your choice.
• Cut and paste the found records to another sheet, spreadsheet, or a specific place in the document with the Move to another location option.
• Decide to Clear values to delete the found entries in the selected key columns and leave all other data intact.
• You can also remove all lines with the detected values within your selection only...
• Or Delete entire rows on the sheet including data outside the selected range.

Click Finish to find duplicate or unique values.

See the result

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.

How to work with scenarios

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.

What is a scenario

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? 🙂

Save the scenario

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:

1. Give your scenario a name that will help you understand what it does at a glance.
2. Schedule your scenario so it runs daily automatically at a certain time period. Find all the details about this option below.
3. Identify a spreadsheet where your scenario will work:
   • Pick [Selected spreadsheet] and this scenario will process records in the file where you start it.
     Note. This option is not available whenever you choose to schedule the scenario. In that case, the scenario has to be tied to a certain file.
   • Specify a certain file for the add-on to handle it each time no matter what spreadsheet you run the scenario from.
   • Select any other sheet from Drive to connect the scenario to that file and process it on each scenario run.
4. Set the sheet for the scenario to process:
   • Choose [Selected sheet] and the add-on will work with the tab that is active when running the scenario.
     Note. This option is not available whenever you choose to schedule the scenario. In that case, the scenario has to be tied to a certain sheet.

     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.

   • Pick any sheet from the list and the add-on will always deal with it, no matter the tab you start the scenario from.
     Note. This doesn't support the [Selected range] option from below. For this range, you will have to start the scenario exactly from the sheet you want to process.
5. Specify what data range should be scanned:
   • If you choose [Selected range], the scenario will be applied to the range you select manually.
     Note. For this range to be processed, make sure to run the scenario from the very sheet you want to modify.
   • [All data] signals the scenario to use the entire used range for work.
   • You can also enter an exact range for the add-on to always handle the same cells.
6. Check if other options you've just selected on steps 2-4 are correct.
   Note. These options cannot be modified. If you'd like to change something here, please restart the add-on to pick the correct settings.

When your scenario is ready, hit Save.

Run your scenario

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:

Manage scenarios

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.

Run scenarios on schedule

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.

How to schedule your scenario

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:

1. Pick the 1st time period from the 1st drop-down to kick-start your scenario automatically at least once a day.
   Note. Due to some Google Sheets peculiarities regarding time-driven triggers, scenarios cannot be run at a certain time on the dot. Instead, they will be completed within scheduled 1-hour intervals.
2. To execute the same settings twice a day, tick off the 2nd run checkbox and choose an additional time interval.
   Note. At the moment, you can schedule only one run per hour. Since Remove Duplicates runtime is limited by 6 minutes in free Google Sheets accounts, this is necessary in order to guarantee successful performance in big datasets for everybody.

   You'll immediately see what time frames are already taken (either by the 1st run of the same scenario or by any other scenario):
   

3. Check the last box if you want to keep the log of the automated scenario performance.

   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.

Manage scenarios timetable

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:

• Click on the Schedule link next to the scenario you want to add to the timetable. You will see the scenario outline and be able to make it run automatically.
  Note. If the time interval is unavailable, it means it's already taken by another scenario:
  
  Since Remove Duplicates runtime is limited by 6 minutes in free Google Sheets accounts, this is necessary in order to guarantee successful performance in big datasets for everybody.
• To change the set time frames or remove the scenario from the timetable, click on that scheduled scenario. The same outline will all the settings will appear letting you adjust what you need.

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.