Methods to Take away Duplicates in Google Sheets

0
39

Google Sheets enables you to take away duplicates out of your doc with three strategies starting from a easy built-in perform to a customized script. Whereas it’s not as obvious as in Excel, Sheets gives a extra various technique to take away duplicates in your spreadsheet.

RELATED: Methods to Take away Duplicate Rows in Excel

Take away Duplicates Utilizing the Distinctive Perform

The primary technique we’re going to take a look at makes use of the built-in Google Sheets perform that finds all distinctive entries, letting you do away with every little thing else within the knowledge set.

Hearth up your browser and open up a spreadsheet to get began.

A sample table with duplicates

Subsequent, click on the empty cell the place you need the info to output, sort =UNIQUE, after which click on on the advised perform that seems within the dialog window.

Select an empty cell and start typing =Unique, then click on the suggestion that appears

From right here, you may both manually enter the vary of cells or spotlight them for the perform to parse. Whenever you’re executed, hit Enter.

Highlight the rows/cells you want the function to search through, then hit Enter

Identical to magic, Sheets picks out all of the distinctive outcomes and shows them within the chosen cell.

The unique entries that were found will appear outside your table below the function's cell

In case you copy and paste the data again right into a Google Sheet, be sure you right-click the place you wish to paste after which select Paste Particular > Paste Values Solely—in any other case, solely the formulation will get copied into the brand new cell.

If you copy and paste the information back into a Google Sheet, be sure to right-click, then choose Paste Special > Paste Values Solely” width=”632″ peak=”238″></p>
<h2>Take away Duplicates Utilizing an Add-On</h2>
<p>For the subsequent technique, you’ll want to put in an add-on to Google Sheets. In case you’ve by no means used an add-on earlier than, they’re just like browser extensions that unlock further extra options for you inside Google apps, like Docs, Sheets, and Slides.</p>
<p>For this instance, we’ll be utilizing Take away Duplicates by AbleBits. It comes with a free trial for 30 days; premium memberships are $59.60 for a lifetime subscription or $33.60 yearly.</p>
<p><strong>RELATED:</strong> <strong><em>The Greatest Google Sheets Add-Ons</em></strong></p>
<h3>Putting in the Add-On</h3>
<p>To get an add-on, open a file in Google Sheets, click on “Add-ons,” after which click on “Get add-ons.”</p>
<p><img class=

Kind “Duplicates” into the search bar after which click on the “Free” button.

Type Duplicates into the search bar, then click Free on the add-on you want to install

Click on on the Google account you wish to use to put in the add-on.

Choose an account to install the add-on

Upon putting in add-ons, you have to grant them particular permissions. These are basic to the operation of the add-on. Be sure you totally perceive the permissions and belief the developer earlier than putting in any add-on.

Click on “Permit.”

Read and review the permissions for the add-on, then click Allow

Utilizing the Add-on

You should utilize the Take away Duplicates add-on for a single column or throughout a number of rows. On this instance, we’ll be on the lookout for duplicates in rows, nevertheless it works principally the identical for a single column.

In your spreadsheet, spotlight all of the rows you wish to seek for duplicates. After that, click on Add-ons > Take away Duplicates > Discover duplicate or distinctive rows.

Highlight the rows you want to search, click Add-ons, point to Remove Duplicates, then click on Find duplicate or unique rows

After the add-on opens, examine to verify the vary listed is right after which click on “Subsequent.”

Check to make sure the range listed is correct, then click Next

Subsequent, select the kind of knowledge you wish to discover. As a result of we’re eradicating duplicates, choose “Duplicates” after which click on “Subsequent.”

Select Duplicates, then click Next

Choose the columns for the add-on to look. In case you didn’t embrace the headers—or possibly your desk doesn’t have any headers in any respect—ensuring to untick the “My desk has headers” possibility. In any other case, the primary row will likely be omitted. Click on “Subsequent.”

If your tables doesn't have headers, deselct My Table Has Headers, then click Next

Lastly, select what the add-on will do with its findings after which click on “End.”

Choose what you want to happen with the results, then click Next

On the outcomes web page, the add-on tells us that 4 duplicate rows had been discovered and eliminated.

The results page showing how many entries were found in your document

Voila! All duplicate rows disappear off of your sheet.

The updated table without any of the duplicated entries

Take away Duplicate Rows with Google Script Editor

The ultimate technique for eradicating duplicates in your sheet entails utilizing Google App Script, a free-to-use cloud-based growth platform for creating customized, lightweight internet functions. Though it entails writing code, don’t let that scare you off. Google offers in depth documentation and even provides you with the script for eradicating duplicates. Simply copy the code, confirm the add-on, after which run it inside your sheet.

RELATED: Methods to Supercharge Your Google Apps with the Script Editor

From the present Google Sheet, click on “Instruments” after which click on “Script Editor.”

Click Tools, then click on Script Editor

Google Apps Script opens in a brand new tab with an empty script.

An empty function for your bound script

Delete the empty perform contained in the file and paste within the following code:

//Removes duplicate rows from the present sheet. perform removeDuplicates() 

Save and rename your script. Hit the “Run” icon once you’re executed.

Click the Run icon

You’ll must evaluation the permissions your script requires and grant it entry your spreadsheet. Click on “Evaluate Permissions” to see what entry this script desires.

Click Review Permissions to view the requested permissions

Settle for the prompts after which click on “Permit” to authorize the script.

Review the permissions, then click Allow

After it finishes working, return to your Sheet and, identical to the earlier strategies, all duplicate entries vanish out of your file!

The duplicate data points have been removed!

Sadly, in case your knowledge is inside a desk—like the instance proven above—this script won’t resize the desk to suit the variety of entries in it, and also you’ll have to repair that manually.


That’s all there’s to it. Whether or not you wish to use the built-in Distinctive perform, a third-party add-on, or create a customized add-on with Apps Script, Google provides you a number of methods to handle duplicates in your spreadsheet.

Leave a Reply