Remove Duplicates

How to Remove Duplicates in Google Sheets

Summary of steps:

=ULIST(colArray, [indexNum], [threshold])
    1. Type or select a range as the colArray.
    2. (Optional) Enter indexNum, which is the column in colArray to analyse for unique values.
    3. (Optional) Adjust the threshold, the percentage above which values are considered duplicates and removed.
    4. Press Enter or Return.

Let's look at an example showing a simple and intuitive method of removing duplicates in Google Sheets using an Flookup function called ULIST. Duplicates can be a extremely costly and, therefore, removing them is an excellent way to improve data quality.

Suppose that you have a list with names of individuals, the cities they live in and their respective salaries as shown below:

Remove duplicates Google Sheets #GetFlookup

This list obviously contains duplicates under the column titled "City". In order to extract a list of individuals without the duplicated cities, we can use the ULIST formula as shown below:

Remove duplicates Google Sheets #GetFlookup

And our results will look like this:

Remove duplicates Google Sheets #GetFlookup

However, as we can see, there is a duplicate that has not been caught because it contains a spelling error; that duplicate is "New Yorke" vs "New York". In order to resolve this, we need to adjust the threshold parameter as follows:

Remove duplicates Google Sheets #GetFlookup

And finally, we have a clean list containing unique values only:

Remove duplicates Google Sheets #GetFlookup

If you are a fan of Google's UNIQUE function, then you will love the power ULIST brings to your spreadsheet.