Microsoft Excel is probably the most used spreadsheet software and among the alternatives available is Google Sheets. Whether you need to create a 5-column spreadsheet or a 50-column one, you can use Excel and Google Sheets. Now, if you have a spreadsheet that contains hundreds of the same values in different rows, you can delete the duplicate rows in Excel and Google Sheets using this simple trick. We often receive spreadsheets which contain multiple duplicate rows. It happens when you merge two or more Excel sheets. Instead of finding out these duplicate rows manually one by one, you can simply delete them all at once.
Delete Duplicate Rows in Excel
Removing all duplicate rows is not that difficult when using Microsoft Excel since it comes with an inbuilt functionality. To get started, you need to create the Excel spreadsheet. After that, go to Data tab and click on Remove Duplicates button.
Now, you will be asked to select the column/s where the duplicate rows are located.
If you are unsure, select one row at a time. Otherwise, choose all the rows if you know what you are doing.
Delete Duplicate Rows in Google Sheets
As Google Sheets doesn’t come with any inbuilt functionality, you need to take the help of a Chrome extension called Remove Duplicates. To install this Chrome extension for Google Sheets, you need to open your Google Drive account, and then over to this page and install it. Then, open the desired spreadsheet > click on Add-ons > select Remove Duplicates > Find duplicates or uniques.
Then you need to select the range of the table. In other words, you can choose the columns and rows range. In step 2 (out of 4), select Duplicates (Find duplicates, exclude the first instances) and go forward. After that, select the column title and hit Next.
In the next step, you need to select Delete rows within selection. After hitting the Finish button, all the duplicate rows will be removed instantly.
When using this addon, you need to know one thing.
Suppose you have a price chart-related spreadsheet which has only two columns, i.e., Goods Name and Price.
| Goods Name | Price |
| Product name 1 | $105 |
| Product name 2 | $75 |
| Product name 1 | $95 |
| Product name 3 | $45 |
If you have the same goods name multiple times in Column A and different prices for the same goods in Column B, and you delete duplicate rows from Column A, it will be a mess. But if you know what you are doing, this tutorial should work fine.
Read: How to add Header and Footer in Excel spreadsheet.
I am using somethink like this in Google Sheets
function removeDuplicatesIn4ThRow() {
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[3] == newData[j][3] && row[4] == newData[j][4]){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}