There are many ways to filter duplicates in Google Sheets that allow you to remove duplicates from your documents, ranging from a simple built-in function to a custom script. Let’s find out How to filter duplicate data in Google Sheet by 3 ways introduced in the article of GhienCongListen The following.
Advertisement
How to filter duplicate data in Google Sheet with UNIQUE . function
The first method we will look at uses the built-in Google Sheet function to find all entries that are unique, as follows:
1. Start your browser and open a spreadsheet to get started.
Advertisement
2. Click on the empty cell you want the data to output, enter =UNIQUEand then click the suggested function that appears in the dialog window.
Advertisement
3. Enter the cell range manually or drag and drop the mouse in the range of cells you want to filter duplicate data. Press Enter.
4. The worksheet selects all unique results and displays them in the selected cell.
5. If you are copying and pasting the information elsewhere, be sure to right-click where you want to paste it and then select Paste Special > Paste values only otherwise, only formulas is copied to the new cell.
How to filter duplicate data in Google Sheet with add-ons
For the next method, you will need to install the Google Sheet add-on. If you have never used add-ons before, they are similar to browser extensions that unlock extra features for you inside Google apps including Google Docs, Google Slides.
To perform how to filter duplicate data in Google Sheet with add-ons. You must first download the utility called Remove Duplicates on Google’s utility app store. Here’s how to download:
1. Download the Google Sheet duplicate data removal utility right on the sheet by pressing the “+” sign on the right side of the sheet or the Add-ons menu on the top bar. Type in Remove Duplicates and click the plus sign to download.
2. Click the Google account you want to use to install the add-on.
3. After installing the add-ons, you need to give them specific permissions. Click Allow (Allow).
As soon as you have everything in place, perform a way to filter duplicate data in Google Sheet with the Remove Duplicates add-on as follows:
1. In your spreadsheet, highlight all the rows where you want to search for duplicates. Then, click Add-ons > Remove Duplicates > Find duplicate or unique rows. To find duplicates in the column select Find duplicate or unique cells.
2. After the add-on opens, check to make sure the range is listed correctly. Then click Next.
3. Select the data type you want to filter, then click Next.
4. Select the columns to search for duplicates. If you don’t include headers or maybe your table doesn’t have any headers at all, make sure to uncheck the “My table has headers” option. Otherwise, the first row will be ignored. Click Next.
5. Finally, select the action with duplicate findings, then click Finish to finish. Options include the following actions:
- Fill with color: highlight with color.
- Add a status column
- Copy to another location (Copy to another location).
- Move to another location.
- Clear values.
- Delete rows within selection.
On the results page, the add-on tells us how many duplicate rows were found and removed.
Remove duplicates in Google Sheet with Google Script Editor
A final method to get rid of duplicate data in your worksheets is to use Google App Script, a free cloud-based development platform for creating custom, lightweight web applications. Although it involves coding, don’t let that scare you.
Google provides extensive documentation and even gives you a script to remove rows with duplicate data. Just copy the code, verify the add-on, then run it inside your sheet.
From the current Google Sheet, click “Tools” and then “Script Editor.”
Google Apps Script opens in a new tab with a blank script.
Remove the empty function inside the file and paste in the following code:
- //Removes duplicate rows from the current sheet.function removeDuplicates() {
//Get current active Spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
//Get all values from the spreadsheet’s rows
var data = sheet.getDataRange().getValues();
//Create an array for non-duplicates
var newData = [];
//Iterate through a row’s cells
for (var i in data) {
var row = data[i];
var duplicate = false;
for (var j in newData) {
if (row.join() == newData[j].join()) {
duplicate = true;
}
}
//If not a duplicate, put in newData array
if (!duplicate) {
newData.push(row);
}
}
//Delete the old Sheet and insert the newData array
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
Save and rename your script. Tap the “Run” icon when you’re done.
You will have to review the permissions your script requires and give it access to your spreadsheet. Click “Review permissions” to see what access this script wants.
Accept the prompt and then click “Allow” to allow the script.
After it finishes running, go back to your Sheets and just like the previous methods, all duplicate entries will disappear from your file.
Unfortunately, if your data is inside a table, this script won’t resize the table to fit the number of entries in it, and you’ll have to fix that manually.
Above are all ways to filter duplicate data in Google Sheet. Whether you want to use Unique functionality, third-party add-ons, or create customizations with Apps Script, Google gives you plenty of ways to manage duplicate data in your spreadsheets.
If readers are interested in topics related to Google Sheet tips, you can refer to some of the articles below:
If you find the article good, please leave Like & Share to support GhienCong Nghe to bring you more useful articles to read. Thanks for watching.
Refer to How-To Geek
Source: 3 ways to filter duplicate data in Google Sheet. Try the programmer’s super cool way of doing things
– TechtipsnReview
