REMOVING DUPLICATES: GOOGLE SHEETS VS FLOOKUP
Brief Introduction
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.
In this post, we shall examine how to remove duplicates using two different methods. Please note that the images we have used are for illustration purposes only.
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 Flookup Data Wrangler > 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:
Select Keep first identified duplicate value or Keep last identified duplicate value, depending on how you want to look down or look up the table, respectively.
Leave the next option set to Compare data in a single column.
Click “Map columns in selection” in order to get the current number columns in your selection.
Enter the "Left_column" index. In this case, we shall leave it one since we aim to analyse the first column in our selection. We shall not adjust "Right_Column" 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.