If you want to remove duplicates and sort your data with a single formula in Google Sheets, you can do this by combining the SORT function with the UNIQUE function.
In this article I will show you multiple ways of combining these two formulas so that you can remove duplicates and sort with a single formula. I will also show you how to sort by multiple columns, and how to reverse the order of sorting.
To use the UNIQUE and SORT functions in a single formula, follow these steps:
- Start by typing your UNIQUE formula, like this: =UNIQUE(A3:A)
- Then use the SORT function to sort the results of the UNIQUE function, like this: =SORT(UNIQUE(A3:A))
SORT UNIQUE formulas in Google Sheets:
Sort data and remove duplicates from a single column
- =SORT(UNIQUE(A3:A),1,true)
Sort data and remove duplicates from a multiple columns
- =SORT(UNIQUE(A3:D),1,true)
Remove duplicates from sorted data (Alternate method)
- =UNIQUE(SORT(A3:D,1,true))
SORT / UNIQUE nested formula
In this lesson we are using multiple functions in a single formula, where one formula is "inside" of another formula. This is called a "nested" function. Another way to describe this, is to say that one formula is "wrapped" around another formula. For example, in the formula below, the UNIQUE function is nested inside of the SORT function, and the SORT function is "wrapped" around the UNIQUE function.
You can use the output of your UNIQUE function as your SORT function range like this: =SORT(UNIQUE(A3:A),1,true)
This article focuses on combining the SORT and UNIQUE functions, but if you want to learn how to use them individually, check out the lessons linked below:
SORT and UNIQUE formula example
Here is a simple example of using the SORT function with the UNIQUE function in Google Sheets.
In this example we have a list of names, which are unsorted and contain duplicates. We will remove the duplicates and sort the data with a single formula.
The task: Remove the duplicate names, and sort the result in ascending order
The logic: Remove the duplicates from the range A3:A, and then sort by column 1
The formula: The formula below, is entered in the blue cell (C3), for this example
=SORT(UNIQUE(A3:A),1,true)
In the image above, you can see that in column C, the list of names is shorter than it is in column a, and this is because the duplicate names have been removed by the UNIQUE function. Also, the names are in alphabetical order because we used the SORT function with the UNIQUE function.
The formula above tells Google Sheets to remove the duplicates from column A, and then to sort the result by the first column in ascending order.
Sort data and remove duplicates from multiple columns
In this example we will remove the duplicates from multiple columns of data and then sort the result by using the SORT function and the UNIQUE function.
When using the UNIQUE function with multiple columns, the entire row that you are referring to must be a duplicate for the function to consider the entry / row a duplicate. For example, if you refer to the range A1:B, then the values from columns A and B have to be the same to be considered a duplicate. If cell A1 and cell A2 are the same, but B1 and B2 are not, then the row is not a duplicate.
In this example we have inventory data that is unsorted and contains duplicate rows. We will remove the duplicates and sort the data with a single formula.
The task: Sort by the product ID in ascending order, and remove the duplicate inventory entries
The logic: Sort by column 1 and remove the duplicates from the range A3:D
The formula: The formula below, is entered in the blue cell (F3), for this example
=UNIQUE(SORT(A3:D,1,true))
In the image above, you can see that the duplicate inventory entries have been removed, leaving a shorter list of items on the right side of the sheet. Also, the data is now sorted by the product ID number.
When using the SORT function and the UNIQUE function, with this particular pair of functions, you can choose which function you want to nest inside the other. In the formula above, we nested the SORT function inside the UNIQUE function, and the formula below is the opposite where the UNIQUE function is nested inside of the SORT function, and both of these formulas do the same thing, as you can see in that images above and below.
=SORT(UNIQUE(A3:D),1,true)
Now you know how to sort your data and remove duplicates from it by using a single formula in Google Sheets!
