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

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 2 million cells.

Tip. This add-on will help you find identical or unique rows in your table.

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.

How to use the Remove Duplicates add-on

Start Remove Duplicates

Open the Add-ons menu, find Remove Duplicates, and click on the Find duplicate or unique rows tool:
Find Remove Duplicates in the Google Sheets menu.

Tip. You can also find Remove Duplicates in the Dedupe & Compare group of Power Tools:

  1. Access Dedupe & Compare from the corresponding Google Sheets menu:
    Find the add-on in Power Tools.
  2. And click the Remove duplicate rows icon on the Power Tools sidebar:
    The Remove Duplicates icon in Power Tools.

Step 1: Select your data

Here you can select the range with the data you want to search in:
Select the range to look for duplicates within.
By default, the add-on highlights the entire table in the sheet of your choice.

Note. If you want to delete duplicates rather than just find and mark them, it's best to select the whole table so that the add-on removes entire rows avoiding data corruption.

You can alter the range by entering the needed address in the Select the range with your table field or by clicking the Select range icon. The latter will open a dialog box and allow you to pick the necessary cells:
A dialog window for you to highlight the necessary cells.

Tip. Use the Auto select button to detect the range with all data you have automatically.

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:
Choose Duplicates to find the replicated data.

  • Pick Duplicates to get rid of any repeating rows in your table. The add-on will keep unique records and the first entries of any duplicates you have. Your dataset will become clear of any extra replicates:
    Find Duplicates.
  • Select Duplicates + 1st occurrences to see the original entries as well as their copies:
    Find Duplicates and 1st occurrences.
  • Choose the Uniques option to find only those rows that don't have any duplicates in the sheet:
    Find Uniques.
  • 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:
    Find Uniques + 1st occurrences.

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:
Choose columns you want to search in.

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.
Tip. If you want to look for complete row duplicates, tick off all columns at once by using the top checkbox in the header row.
  • 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 Case sensitive option to treat the records that differ only in case as uniques.
    Tip. Leading and trailing spaces in cell values are ignored by default.

Click Next to proceed to the last step.

Step 4: Decide what to do with the found values

The add-on allows you to choose one of six ways to deal with the found records:
Color, copy, or remove duplicates from your sheet.
Select the radio button next to the action you'd like to apply:

  • Highlight the rows with the 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 using the Delete rows within selection option.

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:
See the summary with the number of found values.

Tip. If you'd like to run the add-on again, click on the Start over button.

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

Reply

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.

Reply
Alex Maldonado says:
September 9, 2018 at 7:56 pm

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!

Reply

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.

Reply

Post a comment

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.