قالب وردپرس درنا توس
Home / Tips and Tricks / How to Remove Duplicates in Google Sheets

How to Remove Duplicates in Google Sheets



With Google Sheets, you can remove duplicates from your document using three methods, ranging from a simple built-in function to a custom script. While this is not as obvious as in Excel, Sheets offers a more diverse way to remove duplicates in your spreadsheet.

RELATED: Removing Duplicate Lines in Excel

Duplicating Using Duplicate Function

The first method we'll look at uses Google's built-in spreadsheet function, which finds all unique entries so that you can remove everything else from the record.

Start your browser and open a table to get started

 A sample table with duplicates

Then click on the empty cell where you want to output the data = UNIQUE and then click on the proposed function will appear in the dialog box.

 Select an empty cell and start typing = Unique. Then click on the suggested proposal

. Here you can either enter the cell range manually or select it for the function for analyzing. When done, press Enter.

 Select the lines / cells that the function should search for and press Enter

. As with magic, all unique results and sheets are selected. They appear in the selected cell.

 The unique entries found are displayed outside the table below the function cell.

If you copy and paste the information back into a Google Sheet, you must enter it correctly – click where you want to paste, and then choose Paste Special> Paste Only – otherwise it will just copied the formula into the new cell.

 If you copy and paste the information back into a Google Sheet, you need to be sure Click the right mouse button, and then choose Paste Special> Paste Values ​​Only. "width =" 632 "height =" 238 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror; =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> </p>
<h2>  Remove Duplic Using an Add-On </h2>
<p>  The next method requires you to install a Google Sheets add-on if you have never used an add-on before They are similar to browser extensions that allow you to unlock additional additional features for Google Apps such as Docs, Sheets, and Slides. </p>
<p>  In this example, we will do that by using Duplicates from AbleBits to remove it with a free trial for 30 days Premium memberships are $ 59.60 for a lifetime subscription or $ 33.60 per year. </p>
<p><strong>  RELATED: </strong> <strong><em>  The Best Google Sheets Add-Ons </em></strong></p>
<h3>  Installing the Add-On </h3>
<p>  Open a file in Google to get an add-on Click on "Sheets" and then "Add-ons" Click on "Get Add-ons". </p>
<p>  <img class=

Click the "Free" button.

<img class = "alignnone wp-image-413137 size-full" data-pagespeed-lazy-src = "https://www.howtogeek.com/wp-content/uploads/2019/05/x2019-05 -02_16h54_03.png.pagespeed.gp + jp + jw + pj + ws + js + rj + rp + rw + ri + cp + md.ic.aargqQX2o5.png "alt =" Type duplicates in the search bar and click then to the add-on you want to install

 Select an account to install the add-on

After installing add-ons, you must grant them specific permissions. This is essential to running the add-on, so make sure you fully understand the permissions and trust the developer before installing an add-on.

Click Allow.

 Read and verify the permissions on the add-on, and then click Allow

Using the Add-On

You can use the Add-on to remove duplicates for a single column or for me use more rows. In this example, we search for duplicates in rows, but it essentially works for a single column.

In your spreadsheet, select all the rows you want to search for duplicates. Then click Add-ons> Remove Duplicates> Find Duplicate or Unique Rows.

 Select the rows to search, click Add-ons, point to Remove Duplicates, and then click Find Duplicate or Unique Rows.

After opening the add-on, verify that the specified range is correct, and then click Next.

 Make sure the specified range is correct, and then click Next .

Next, select the data type you want to search. Since duplicates are removed, select "Duplicates" and click "Next."

 Select Duplicates and click Next.

Select the columns for the add-on to search. If you did not add the headers – or perhaps your spreadsheet has no headers at all – deselect the option "My spreadsheet has headers". Otherwise, the first line is omitted. Click on Continue".

 If your spreadsheets have no headers, deselect

Finally, select what the add-on should do with its results. Click Finish.

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

. On the results page, the add-on finds and removes four duplicate rows.

 The results page shows how many entries were found in your document.

Voila! All duplicate rows are removed from your worksheet.

 Updated table without duplicate entries

Remove duplicate lines with Google Script Editor

The last method to remove duplicates in your worksheet is to use Google App Script, a free Cloud-based development platform for creating custom, lightweight web applications. Even if you have to write code, do not be put off by it. Google offers extensive documentation and even provides you with the duplicate removal script. Just copy the code, check the add-on, and then run it on your worksheet.

RELATED: To download your Google Apps using the Script Editor

from the current Google, click "Tools," then "Script Editor."

 Click

. Google Apps script opens in a new tab with empty script.

 An empty function for your bound script

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

  // Removes duplicate rows from the current worksheet.

Function removeDuplicates () {
// Get the current active spreadsheet
var sheet = SpreadsheetApp.getActiveSheet ();
// Get all values ​​from the rows of the table
var data = sheet.getDataRange (). getValues ​​();
// Create an array for non-duplicates
var newData = [];
// Iterate through the cells of a row
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 it is not a duplicate, insert the newData array
if (! duplicate) {
newData.push (line);
}
}
// Delete the old sheet and paste the newData array
sheet.clearContents ();
sheet.getRange (1, 1, newData.length, newData [0] .length) .setValues ​​(newData);
} 

Save the script and rename it. Click the "Run" icon when done.

 Click the

icon to verify the permissions your script requires and grant you access to your spreadsheet. Click "Check Permissions" to see what access this script wants.

 Click Check permissions to view the required permissions

Accept the prompts and click Allow to authorize the script.

 Check the permissions, and then click Allow

After completing the execution, go back to your sheet, and as with the previous methods, all duplicate entries are removed from your file!

 The duplicate data points have been removed!

Unfortunately, if your data is in a table (as in the example above), this script does not change the table to the number of entries that it manually fixes.


That's all that goes with it. Whether you use the built-in Unique feature of a third-party add-on or you want to create a custom add-on with Apps Script, Google offers you several ways to manage duplicates in your spreadsheet.




Source link