Table of Contents
Introduction
Duplicates or duplicate entries are a prevalent problem for many people when it comes to keeping data entries. Consider the following scenario: you have the names and contact information of persons who responded to a survey. There’s a chance you’ll have duplicate records if you list them in a spreadsheet program like Google Sheets. In that instance, one person may have completed the survey multiple times, resulting in Google Sheets listing the entry twice. When it comes to enterprises, such identical listings are much more difficult. Consider what happens if a cash transaction is recorded many times. It would be a problem if you used that data to determine total expenses.
To avoid such issues, make sure the spreadsheet doesn’t have any duplicate records. What is the best way to accomplish this? In this post, you’ll learn how to eliminate duplicates in Google Sheets in different ways. Come on, without further ado, let us take a look at the subject.
If you are looking to remove duplicates in Google sheets, this article is for you.
Steps To Remove Duplicates In Google Sheets
Following are the steps to remove duplicates in Google Sheets:
Method 1: Using the Remove Duplicates Option
Step 1: Take a look at this example (see screenshot below). As you can see, the record “Ajit” has been entered twice. This is a duplicate of the previous record.
Step 2: Select or highlight the rows and columns to eliminate duplicate entries. Now select Data from the menu options. Scroll down, then go to data cleanup and click on the Remove duplicates option. Or instead of data click on help and type remove duplicates.
Step 3: A pop-up box will appear, prompting you to choose which columns to examine. Select the options that best suit your requirements, then click the Remove Duplicates button.
Step 4: All duplicate records would be removed, leaving just unique elements. The amount of duplicate records that were removed will be displayed in Google Sheets.
Step 5: Only one duplicate entry was deleted in our situation (Ajit). The duplicate entry has been eliminated by Google Sheets, as you can see (refer to the screenshot that follows).
Method 2: Remove Duplicates with Formulae
Formula 1: UNIQUE
For instance: =UNIQUE (A2:B6)
Step 1: This would look for duplicate entries in the range of cells specified (A2:B7). Enter the given formula in any empty cell in your spreadsheet. The range of cells you specify will be highlighted in Google Sheets.
Step 2: The unique records where you typed the formula will be listed in Google Sheets. The old data can then be replaced with unique records.
Formula 2: COUNTIF
Step 1: Take a look at the screenshot below, which contains one duplicate entry.
Step 2: Enter the formula =COUNTIF(A$2:A2, A2)>1 in cell C2 of the above screenshot.
When you press the Enter key, the result will now be shown as FALSE.
Step 3: Place the mouse pointer over the little square at the bottom right corner of the selected cell. Instead of your mouse pointer, you will now see a + symbol. Click and hold that box, then drag it up to the cell where the duplicate entries should be found. The formula would be copied to the remaining cells using Google Sheets. Or, if you get the option for autofill as shown above, then select it.
Step 4: When a duplicate entry is found, Google Sheet will automatically append the word “TRUE” in front of it.
NOTE: We have specified as >1 in this condition (greater than 1). As a result, this condition will be TRUE if an entry is found more than once. The result is FALSE everywhere else.
Conclusion
In this article, we have outlined all the steps in order to remove duplicates in Google sheets. If you have any other queries or suggestions, let us know in the comment area.
0 Comments