Large spreadsheets with a lot of entries often accidentally have multiple instances of the same data. MakeUsOf offers a handy script that makes it easy to remove duplicates in Google’s Sheets spreadsheet.
In Google Drive, go to Spreadsheet and click Tools -> Script Editor. Then go to File -> New -> Script File and create a new script called “removeDuplicates” (without the quotes). Copy and paste this into the main body:
function removeDuplicateRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
if(row.join() == newData[j].join()){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
Hit Ctrl+S to save and close the tab.
To run the script, in a Spreadsheet, go to Tools -> Script Manager, where you will see removeDuplicates as one of the scripts. Select it and hit Run. The first time you run it, it will ask for authorisation.
The script only removes duplicates in rows and not columns, and takes a few minutes. But it gets the job done quite effectively.
Boost Productivity With These Excellent Google Spreadsheet Scripts [MakeUseOf]
Comments