قالب وردپرس درنا توس
Home / Tips and Tricks / To delete duplicate files in Google Sheets

To delete duplicate files in Google Sheets



With Google Sheets, you can remove duplicate documents from your document using three methods ranging from a simple integrated feature to a custom script. While it's not as clear as in Excel, Sheets offers a more versatile way to delete duplicates in the worksheet.

RELATED: Deleting duplicates in Excel

Removing duplicates with the unique feature

first method to look at uses the built-in Google Sheets feature that finds all unique records, so you get rid of with everything else in the dataset.

Turn on your browser and open a spreadsheet to get started

 A test table with duplicates

Then click on the empty cell where you want the data to be output, type = UNIQUE and then click on the suggested function shown in the dialog.

 Select an empty cell and start typing = Unique and then click on the proposal shown

From here you can either manually enter the cell range or select them for the function to analyze. When finished, press Enter.

 Select the rows / cells you want the function to search through, and then press Enter

Just like magic, select Sheets all unique results and display them in the selected cell.

 The unique information found will appear outside your table below the function cell

If you copy and paste the information back into a Google sheet, be sure to right-click where you want paste and then select Paste special> Paste values ​​only – otherwise only the formula will be copied to the new cell.

 If you copy and paste the information back to a Google sheet, be sure to right-click and then select Paste Special> Incl. values ​​"width =" 632 "height =" 238 "src =" / sidorpeed_static / 1.JiBnMqyl6S.gif "onload =" sidespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); = "this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" /> </p>
<h2>  Remove Duplic ates Using an Add-on </h2>
<p>  For the next method, you must install an add-on to Google Sheets. If you've never used an add-on before, the browser extensions that unlock extra features are similar to you inside Google apps, such as documents, discs, and slideshows. </p>
<p>  For this example, we will be using AbleBits Remove Duplicates. It comes with a free trial for 30 days; Premium membership is $ 59.60 for a lifetime subscription or $ 33.60 annually. </p>
<p><strong>  RELATED: </strong> <strong><em>  The best additions to Google Sheets </em></strong></p>
<h3>  Installing the extension </h3>
<p>  To get extensions, open a file in Google Ark, click "Add-ons" and then click "Download Add-ons". </p>
<p>  <img class=

Type "Duplicates" in the search bar and then click the "Free" button.

 Type Duplicate in the search field and then click Free on the extension you want to install

Click on the Google account you want to use to install the extension.

 Select an account to install the extension

When installing extensions, you must grant them specific permissions. These are fundamental to the function of the supplement. Make sure you fully understand the permissions and trust the developer before installing any extension.

Click "Allow."

 Read and review the permissions of the extension, and then click Allow

Usage Add-on

You can use Add Duplicate Remove Duplicates for a single column or over multiple lines. In this example, we search for duplicates in rows, but it works mostly for a single column.

In all worksheets, select all rows you want to search for duplicates. Then click Add-ons> Delete Duplicates> Find Duplicates or Unique Rows.

 Select the rows you want to search, click Add-ons, point to Delete Duplicates, and then click Find Duplicates or Unique Rows

When the extension opens, make sure the specified range is correct and then click "Next".

 Make sure the specified range is correct and then click Next

Then select the type of data you want to find. As you delete duplicates, select "Duplicates" and then click "Next".

 Select Duplicates and then click Next

Select the columns for the extension to search. If you didn't include the headline – or maybe your table has no headlines at all – be sure to uncheck the "My table has headings" option. Otherwise, the first line will be omitted. Click "Next".

 If your tables do not have headers, deselect My table Have headings and then click Next

Finally, choose what the extension will do with its results and then click "Finish."

 Choose what you want to do with the results and then click Next

On the results page, the add-on tells you that four double rows were found and removed.

 The results page shows how many records were found in your document

Voila! All double lines disappear away from your sheet.

 The updated table without any duplicate entries

Delete duplicate rows with Google Script Editor

The last method of deleting duplicates in your sheet involves using Google App Script, a platform for development platform that is not useful for creating custom, lightweight web applications. Although it's about writing code, don't let that scare you. Google provides comprehensive documentation and even delivers you with the script to remove duplicates. Just copy the code, verify the add-ons, and then run it in your sheet.

RELATED: How to upload your Google Apps with Script Editor

From the current Google Sheet, click "Tools" and then click "Script Editor."

 Click Tools, and then click Script Editor

Google Apps Script opens in a new tab with a blank script.

 An empty function for your bound script

Delete the empty function inside the file and paste in the following code:

  // Deletes duplicate rows from the current sheet.

function removeDuplicates () {
// Download current active spreadsheets
var ark = SpreadsheetApp.getActiveSheet ();
// Get all values ​​from the spreadsheet rows
var data = sheet.getDataRange (). getValues ​​();
// Create an array for non-duplicates
var newData = [];
// Iterate through a row of cells
for (i was in the data) {
varrad = data [i];
was double = false;
for (where j in newData) {
to (row.join () == newData [j] .join ()) {
duplicate = true;
}
}
// If not a duplicate, insert the newData array
about (! duplicates) {
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. Click the "Run" icon when done.

 Click the Run icon

You must review the permissions that your script requires and give it access to the worksheet.

 Click Review permissions to view the requested permissions

Accept the prompts, and then click "Allow" to accept the script.

 Review the permissions and then click Allow

When it's ready, go back to your sheet and, just like previous methods, all duplicate entries will be removed from your file!

 Duplicate data points have been removed!

Unfortunately, if your data is inside a table, like the above example, this script will not resize the table to fit the number of records in it, and you have to fix it manually.


That's all there is. If you want to use the integrated Unique feature, a third-party add-on or create a custom extension with Apps Script, Google gives you several ways to manage duplicates in the spreadsheet.




Source link