How to find duplicates in Google Sheets through different ways

While dealing with a raw set of data, it is normal to come across records having the same set of entries multiple times due to clerical errors or mistakes during data extraction. Sometimes, it is essential to get rid of such repetitive records in a spreadsheet, and if you are on Google Sheets, you have different ways to get rid of such same entries. 

Google introduced some customized ways to remove similar entries in a datasheet, which makes it easy even for a newbie to find and delete them on Google Sheets. Hence, I will discuss how to find duplicates in Google Sheets. As there are several ways to delete similar and repetitive entries in Google Sheets, I will discuss all the ways so that you can use the different ways based on your specific use-case scenario.

So, without any further delay, let’s get started with the different ways how to find duplicates in Google Sheets.

Using the ‘Remove duplicates’ option

If you are having a raw set of data, and you want to delete those records based on one or two specific fields, you can use this method to delete such records.

Say, for example, there is a set of data where there are roll numbers of multiple students, along with the marks they have obtained in the final examination. In this situation, you can delete all the records, where there are multiple entries of the same student, i.e. the entries with the same roll number.

Going with the above example, we have the roll number of the students in column A, and the marks they have obtained in column B, along with a header row in row number 1.

To get started, select all the records, as shown in the screenshot below, and hence, click on ‘Data’ in the menu bar, hover over to ‘Data cleanup’, and finally click on ‘Remove duplicates’.

In the options that appear, here are a few things that you should understand.

As we have selected all the records, including the row that has the headings, we have to mark ‘Data has header row’. However, if you haven’t selected the header row, don’t put a tick here.

Next, we have to assign the columns that have to be analyzed to remove the records. As the records will be deleted based on the repetitive roll numbers, we have to mark ‘Column A’, where the roll numbers are present. Hence, click on ‘Remove duplicates’.

In the case you want to delete records based on values in multiple columns, select the additional columns too. If you want to delete identical records, or the records, where both the roll number and the marks obtained are the same, as per the above example, tick on ‘Select all’.

All the duplicate records will be deleted instantly or may take some time based on the size of the data, and the number of identical records.

How to find duplicates in Google Sheets using the ‘UNIQUE’ function

If you want to delete identical records, i.e., the records which are similar in every respect, you can also use the UNIQUE function.

Using the UNIQUE function, the new set of records without duplicates will be placed in another range of cells.

Going with the above example, if we want the set of data without the duplicates in the range D2 to E15, the formula in cell D2 will go as follows.

=UNIQUE(A2:B15)

You will instantly get the unique records in the cells starting from D2. If the current is filled with data, and the unique records can’t be populated in the same sheet, you can also get the unique records in another sheet. You simply need to insert the name of the sheet before the range.

So, if the data is present in Sheet1, and you want to get the unique records in the cells starting from D2 in Sheet2, the formula in cell D2 in Sheet2 will go as follows.

=unique(Sheet1!A2:B15)

Here Sheet1! is used as an identifier that refers to the mentioned range in Sheet1 instead of the current one. The unique records will be instantly in the cells starting D2 in Sheet 2. 

So, that’s how to find duplicates in Google Sheets using the UNIQUE function and strip out identical records from a Google Sheets file.

Finding similar records through sorting

All spreadsheet programs including Google Sheets have a sorting function that arranges the records based on a set criterion.

In the case of the above example, if we sort the records in ascending order of roll numbers, records with the same roll numbers will appear back-to-back, and you can easily delete duplicate records by identifying and hence selecting each of them.

If the data has a limited set of records, say around a hundred, you can first sort the records, and hence easily delete the duplicate ones. However, if there is a huge number of records, this will become complex, and time-taking to visually identify the same records and hence purge them. But it is still a way to recognize duplicate records and delete them if you have a limited set of records.

So, those were the different ways how to find duplicates in Google Sheets. Do you have any questions? Feel free to comment on the same below.

Other Articles:

• How to use the FLOOR and CEILING functions in Google Sheets
• How to use VLOOKUP in Google Sheets
• How to calculate the standard deviation & mean of data in Google Sheets
• Format Google Sheets cells to use the Rupee (₹) symbol
• How to create a sortable table in Google Sheets to organize data