How to Sort Unique List in Excel (10 Useful Methods)

When you have a larger dataset and you need to sort the dataset based on unique values, that is to say, you want to skip duplicate values. Getting such type of output was quite difficult in the earlier version of Excel but it has become an easy task if you are using Excel 365. In this article, I’ll discuss 10 useful methods to sort datasets based on unique values in Excel.


Download Practice Workbook 


10 Suitable Methods to Sort Unique List in Excel

This is our today’s dataset where Employee Name is provided with their unique ID No., Salary & Rank.

Dataset

Let’s drive into methods.


1. Finding Unique List Sorted 

Here, I’ll show two separate ways to get sorted unique lists.

1.1. Using Advanced Filter

Advanced Filter is really a sophisticated filtering tool instead of AutoFilter that screens data based on specified criteria.

So, let’s see the application of the Advanced Filter tool to sort the Employee Name field.

The procedure will be as follows-

⇰ Select the whole dataset.

⇰ Go to Data tab > Choose the Advanced option from the Sort & Filter ribbon

⇰ Fix the List range as $C$5:$C$14.

⇰ In this case, we have no criteria. So keep the Criteria range blank.

⇰ Check the circle before the Copy to another location option, later you have to fix the location for getting the filtered data (e.g. $F$5).

⇰ As we want to get unique records, always check the box before the Unique records only option.

⇰ Finally, press OK.

Sort Unique List Using Advanced Filter

So, the sorted unique list for the name is as follows.

Sort Unique List Using Advanced Filter


1.2. Combination of the SORT & UNIQUE Function

Although the Advanced Filter is an updated tool for filtering, Excel 365 (also Excel web and Excel 2021) introduces us to UNIQUE & SORT functions. The UNIQUE function returns a list of unique values from a given dataset or cell range whether the SORT function can sort the dataset in a specified order.

More importantly, we can get the sorted unique data for a specific field like Employee Name which is not possible while using the Advanced Filter as it extracts all fields of the source dataset. So, we can use the following formula for getting the sort unique list.

=SORT(UNIQUE(C5:C14))

Here, C5:C14 is the cell range for the name of the employee.

While explaining the above formula, we can say that the UNIQUE(C5:C14) syntax returns unique values and then the SORT function sorts the found unique values in ascending order.

Combination of the SORT & UNIQUE Function

The above picture depicts the sorted unique list of the name of the employee.

Read More: How to Sort and Filter Data in Excel (A Complete Guideline)


2. Sort Unique List on the Basis of a Value

Assuming that a value (e.g. a value of Rank) is given and we have to find the sorted unique data. Also, we’ll use the two methods.

2.1. Using Advanced Filter

In the case of the Advanced Filter,

After opening the Advanced Filter dialog box, fix the List range as $B4:$D14 and the Criteria range as $F4:$F5.

Sort Unique List on the Basis of a Value

If you press OK, you’ll get the following output.

Sort Unique List on the Basis of a Value


2.2. Using Function

You may use the following formula when you want to get the sorted unique list on the basis of a given value.

=SORT(UNIQUE(FILTER(C5:C14, F5=D5:D14)))

Here, C5:C14 is the cell range for the name of the employee, F5 is the given value, and D5:D14 is the cell range for the Rank field.

In the FILTER function, I fixed C5:C14 as an array, and F5=D5:D14 which includes the specific value, thus the function will filter the dataset.

Then the UNIQUE function will return the unique value of the filtered data.

Finally, the SORT function will sort the found unique values in ascending order.

After entering the formula if you press Enter, you’ll find the following output.

Sort Unique List on the Basis of a Value

Read More: Sort Column by Value in Excel (5 Methods)


3. Sort Unique List on the Basis of a Cell Range

Furthermore, if you want to get the sorted unique list based on a cell range, you also can find that using the two ways.

3.1. Using Advanced Filter

After opening the Advanced Filter dialog box, fix the List range as $B$4:$D$14 and the Criteria range as $F$4:$G$5.

List on the Basis of a Cell Range

So, the output will be as follows.

List on the Basis of a Cell Range

Read More: How to Sort ListBox with VBA in Excel (A Complete Guide)


3.2. Using Function

While using the functions, insert the following formula.

=SORT(UNIQUE(FILTER(C5:C14,(D5:D14>=G5)*(D5:D14<=G6))))

Here, C5:C14 is the cell range for the name of the employee, G5 is the given first value, G6 is the second value, and D5:D14 is the cell range for Rank field.

Excel Sort Unique List on the Basis of a Cell Range


4. Sort Unique List Relying on a Single Criteria

Imagine, you want to sort with unique values if the salary is greater than or equal to $50000.

4.1. Using Advanced Filter

In the case of the Advanced Filter,

After opening the following dialog box, specify the List range as $B$4:$D$14 and the Criteria range as $F$4:$F$5.

List Relying on a Single Criteria

So, the below picture shows the sorted unique list.

List Relying on a Single Criteria

Read More: How to Use Advanced Sorting Options in Excel


4.2. Using Function

While using the function, just enter the below formula

=SORT(UNIQUE(FILTER(C5:C14, D5:D14>=F5)))

Here, C5:C14 is the cell range for the name of the employee, F5 is the given value, and D5:D14 is the cell range for the Rank field.

List Relying on a Single Criteria


5. Sort Unique List Relying on Multiple Criteria

Say, you have to sort the dataset for which salary is greater than or equal to $50000 and rank is equal to 2.

5.1. Using Advanced Filter

In the case of the Advanced Filter,

After opening the dialog box, set the List range as $B$4:$E$14 and the Criteria range as $G$4:$H$5.

Sorting Unique List Relying on Multiple Criteria

If you do that, the following output will be found.

Sorting Unique List Relying on Multiple Criteria

Read More: How to Auto Sort Multiple Columns in Excel (3 Ways)


5.2. Using Function

While using the functions, just insert the following formula.

=SORT(UNIQUE(FILTER(C5:C14,(D5:D14>=G5)*(E5:E14=H5))))

Here, C5:C14 is the cell range for the name of the employee, G5 is the for the required salary, H5 is the second value, D5:D14 is the cell range for the Salary field, and E5:E14 is the cell range for the Rank field.

Sorting Unique List Relying on Multiple Criteria


Similar Readings:


6. Creating Dynamic Sorted Unique List 

Sometimes, we need a dynamic list where the list will be updated automatically if any entry is inserted.

Interestingly, we may prepare such type of dynamic list for the sorted unique data.

For doing this, we have to create an Excel table.

⇰ Select the entire dataset.

⇰ Go to the Insert tab > click on Table.

⇰ Also, check the box before the My table has headers.

Creating an Excel Table

⇰ Now, the created table(Table1) is stored in Excel, and we can call the table anytime.

⇰ Then insert the following formula.

=SORT(UNIQUE(Table1))

Here, Table1 is the created table for the source data.

Creating Dynamic Sorted Unique List

⇰ If you press Enter, you will get the following dynamic list of sorted unique data.

Creating Dynamic Sorted Unique List

Again, you can check the dynamic list whether it is working or not.

For example, insert new data (ID: 1008) in the last of the existing dataset, and you see the list is automatically updated.

Creating Dynamic Sorted Unique List


7. Sort Unique List from A to Z (Alphabetically)

If you skip the Sort Order argument in the SORT function, it will automatically return from A to Z(ascending order).

So the formula will be-

=SORT(UNIQUE(B5:D14))

Here, B5:D14 is the dataset.

Unique List from A to Z(Alphabetically)

Read More: How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)


8. Sort Unique List from Z to A

But if you need to sort the dataset from Z to A (descending order), you have to insert the value of Sort Order argument in the formula

So the adjusted formula will be-

=SORT(UNIQUE(B5:D14), ,-1)

Here, B5:D14 is the dataset, and -1 is for the descending order.

Unique List from Z to A

Read More: How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)


9. Sort Unique List Horizontally

When you need to sort the dataset horizontally that means in a row, surely based on unique values, you may utilize the below formula.

=TRANSPOSE(SORT(UNIQUE(C5:C14)))

Here, C5:C14 is the name of the employee.

Sort Unique List Horizontally

Read More: How to Sort Drop Down List in Excel (5 Easy Methods)


10. When Some Cells are Blank

In some cases, you may have blank cells in your dataset.

Obviously, we need to ignore the cells while getting the sort unique list in Excel.

So the formula will be as follows-

=SORT(UNIQUE(FILTER(C4:C14,C4:C14<>"")))

Here, C4:C14 is the name of the employee, ” ” is for ignoring blank cells.

When Some Cells are Blank

Read More: Excel Sort and Ignore Blanks (4 Ways)


Things to Remember

Name of Errors When Occurs
#CALC! If the UNIQUE function cannot extract the unique values.
#SPILL! If there is any value in the spill range where the UNIQUE function will return the list.
#VALUE! The SORT function occurs if the output (sorted unique values) is not available in the given dataset.

Conclusion

This is how you may get the sort unique list in Excel efficiently. Now, choose any method based on your preference. I hope this article will ease your Excel Journey.

Enjoy Excelling!


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo