How to Find and Remove Duplicates in Google Sheets (5 Ways)

Sometimes we fell into such a situation when we have a dataset that has some duplicate values in it. If we calculate keeping the duplicate values, the result will be incorrect. So we have to find and remove them. In this article, we’ll know about how to find and remove duplicates in Google Sheets with 5 easy tricks. Moreover, we’ll learn about highlighting duplicate values and the use of the VLOOKUP function to find and remove duplicates.

A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.

5 Quick Tricks to Find and Remove Duplicates in Google Sheets

First, let’s get introduced to our dataset. Here we have some Product ID in Column B and Product in Column C. Some Products are duplicates in this dataset. Now we’ll learn 5 quick and easy methods to find and remove them from the dataset.

1. Using the UNIQUE Function

We can do this easily by using the UNIQUE function. This function gives output very quickly. It will filter out the unique values by removing duplicates at a time.

Steps:

• Type the following formula in Cell B17-

• Hit Enter to get the output.

Read More: Google Sheets Use Filter to Remove Duplicates in Column

2. Applying Remove Duplicates Command

There is a Remove duplicates command in Google Sheets which gives the unique values with just one click. You don’t have to use any function like the previous one. You can do it easily with some clicks.

Steps:

• Go to Data > Data cleanup > Remove duplicates.

• You’ll find the following window. Select Data has header row because we have a header row.
• Now, click on the Remove duplicates Button.

• You’ll find the following information as to how many duplicates are found and removed.
• Click the OK Button.

• You’ll get the desired result.

Read More: How to Remove Duplicates in a Column in Google Sheets (5 Ways)

3. Assigning QUERY and MIN Functions Together

Moreover, you can use the QUERY and MIN functions together to get the duplicate values removed. This is like the first case but here we use 2 functions together to get the output.

Steps:

• Write the following formula in Cell B17-

• Press Enter to get the output.

Read More: Remove Duplicates in Column on Different Sheets in Google Sheets

4. Utilizing the Apps Script Extension

Along with the previous methods, we can use the Apps Script extension to find and remove duplicate values. This is a unique feature of Google Sheets. We write some code and the result is automatic. But for applying this method we have to start our dataset from Cell A1.

Steps:

• Start the whole dataset from Cell A2. the header row will be in Row 1 and the values will start from Cell A2.

• Go to Extensions > Apps Script.

• Write the following Code, rename the file and click Run.

function duplicateremoval() {
  var dataset = SpreadsheetApp.getActiveSheet();
  var input = dataset.getDataRange().getValues();
  var newinput = [];
  for (var i in input) {
    var row = input[i];
    var duplicate = false;
    for (var j in newinput) {
      if (row.join() == newinput[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      newinput.push(row);
    }
  }
  dataset.clearContents();
  dataset.getRange(1, 1, newinput.length, 
  newinput[0].length).setValues(newinput);
}

• You’ll get a unique product list.

5. Creating Pivot Table and Using the COUNTA Function

We can take help from the Pivot table to find the duplicate values. In this case, we also have to use the COUNTA function because we want to know about the number of duplicate values.

Steps:

• Go to Insert > Pivot table.

• Select the Data range. Here we have data from Cell B4 to Cell C14.
• Select the Existing sheet because we want to create the Pivot table in the current sheet.
• Give the location which is Cell B16.
• Click Create Button to create the Pivot table.

• In the Pivot table editor window select Add and Product to add them under the Rows menu.

• Under the Values menu also Add Product.

• Then select the COUNTA function under the Summerize by menu.

• You’ll get the following Pivot table. The table will give you a summary of the products. From there you can find and remove the duplicate values manually.

How to Highlight Duplicates by Using the COUNTIF Function

We can highlight the duplicate values by the COUNTIF function. This function helps us when we have to highlight the duplicates and want to show them in the dataset.

Steps:

• Go to Format > Conditional formatting.

• Select data range. Here the range is from Cell C5 to Cell C14.
• Select Custom formula is under the Format rules menu and type the following formula-

• Select green color because our highlighted color will be green and click Done Button.

• You’ll get the duplicate cells highlighted with green color.

Read More: How to Remove Duplicates in Google Sheets Without Shifting Cells

How to apply VLOOKUP Function to Find and Remove Duplicates

Suppose, we have 2 product lists A and B. Some products are common in both lists. Now we have to find and remove the duplicates. We can do it by the VLOOKUP function. It will return the #N/A error for the unique values of list A and the same value for the duplicates. After that, we can remove them manually.

Steps:

• Type the following formula in Cell D5-

• Hit Enter to get the output.

• Then apply the Fill Handle tool to apply the formula in all columns.

• The unique values will show as #N/A. Then you can easily find the duplicates and remove them manually by selecting those rows manually.

Read More: How to Remove Duplicates in Google Sheets Using Formula

Conclusion

That’s all for now. Thank you for reading this article. In this article, we have learned about how to find and remove duplicates in Google Sheets very easily. If you have any queries about this article, please comment in the comment section. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.

Related Article

• How to Remove Both Duplicates in Google Sheets (2 Easy Ways)