REMOVING DUPLICATES IN GOOGLE SHEETS: DEFAULT FUNCTION VS FLOOKUP
Removing duplicates is one of the most important operations you can do when cleaning your data.
Every time we transfer data from one resource to another or merge different datasets, duplicates are bound to arise. Removing these text entries can lead to tremendous benefits like improved customer satisfaction, reduced labour costs and increased efficiency of your data management system.
Removing Duplicates Using the Default Function
Google Sheets has an excellent function for removing duplicates and it can be great option for getting the job done quickly.
To begin, let us select our data as shown below:
In your spreadsheet menu, head to Data > Data clean-up > Remove duplicates. This will open a window where you will enter you will notice that the different columns are selected:
Adjust the parameters to match what you want to do. In our example, we want to remove duplicates by Column B, so we shall make adjustments accordingly:
When we click "Remove duplicates", Flookup will produce a dataset that is free of duplicates, as shown below:
Removing Duplicates Using Flookup
In some cases, you have a list of data that you merged from different sources and are absolutely sure that there are fuzzy duplicates in them. So, you scan through the list and find duplicates but, to your utter frustration, the duplicates are the kind that can be easily missed e.g. “Isaac Newton” Vs “Netwon Issac”.
Using the default method above will not help with these kinds of duplicates. Therefore, to continue cleaning data, we shall use Flookup.
Let us assume that we want to remove duplicates by Column A i.e. "Client Name".
In the table above, there are a couple of duplicates that have different spellings. By using Flookup, we can choose to remove them by percentage similarity or by sound similarity. In this example, we shall use the former.
Go to Extensions > Flookup > Remove duplicates and click "By percentage". This will open the sidebar that is shown below:
This is the window where we will adjust parameters to suit our case. Do the following:
Click “Map columns in selection” in order to get the current number columns in your selection.
Enter the “Index One” value. In this case, we shall leave it one since we aim to analyse the first column in our selection. We shall not adjust "Index Two" since we are comparing data in a single column.
Adjust the "Threshold" value. In our example, we shall use a value of 0.8.
Click "Remove duplicates".
All duplicates in the first column of the selection, with a percentage similarity of 0.8 between them, will be deleted.