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.

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

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

Let’s dive 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.

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

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.

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.

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

Read More: How to Sort by Last Name in Excel

2. Sort Unique List on the Basis of a Value

Assuming that a value (e.g. a value of Rank) is given, then 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.

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

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.

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.

Read More: How to Sort Duplicates in Excel

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.

So, the output will be as follows.

3.2. Using Function

While using the functions, insert the following formula.

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.

Read More: How to Sort by Name in Excel

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.

So, the below picture shows the sorted unique list.

4.2. Using Function

While using the function, just enter the below formula

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.

Read More: How to Sort Numbers with Letter Suffix in Excel

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.

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

5.2. Using Function

While using the functions, just insert the following formula.

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.

Read More: How to Arrange Numbers in Ascending Order with Excel Formula

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.

To do 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.

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

⇰ Then insert the following formula.

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

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

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.

Read More: How to Sort Merged Cells in Excel

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

Here, B5:D14 is the dataset.

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-

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

Read More: How to Sort in Excel by Number of Characters

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.

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

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 of unique list in Excel.

So the formula will be as follows-

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

Things to Remember

Download Practice Workbook 

Conclusion

This is how you may get the sort of 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

• How to Sort Numbers in Excel
• How to Put Numbers in Numerical Order in Excel
• How to Sort Merged Cells of Different Sizes in Excel

<< Go Back to Sort in Excel | Learn Excel