DEFAULT FUNCTION VERSUS 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.

An illustration showing duplicate removal in a spreadsheet

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:

A small spreadsheet section showing a list of fictional characters and their respective emails in Google Sheets

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:

The "Remove duplicates" function window in Google Sheets with default parameters 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:

The "Remove duplicates" function window in Google Sheets with adjusted parameters selected

When we click "Remove duplicates", Flookup will produce a dataset that is free of duplicates, as shown below:

A section showing unique spreadsheet values in Column B

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".

A small spreadsheet section showing a list of fictional characters and their respective emails in Google Sheets (Repeat)

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:

A sidebar that is used for removing duplicates by percentage similarity using Flookup

This is the window where we will adjust parameters to suit our case. Do the following:

  1. Click “Map columns in selection” in order to get the current number columns in your selection.

  2. 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.

  3. Adjust the "Threshold" value. In our example, we shall use a value of 0.8.

  4. Click "Remove duplicates".

A section showing unique spreadsheet values in Column A

All duplicates in the first column of the selection, with a percentage similarity of 0.8 between them, will be deleted.

You Might Also Like