Md. Abdul Kader
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.
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.
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.
=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.
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.
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.
=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.
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.
So, the output will be as follows.
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.
Read More: How to Do Advanced Sorting in Excel (9 Suitable Examples)
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
=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.
Read More: How to Use Advanced Sorting Options 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.
=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.
Read More: Difference Between Sort and Filter in Excel
Similar Readings
- Sum Using OFFSET and MATCH in Excel (With Alternative Options)
- Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
- How to Arrange Numbers in Ascending Order in Excel Using Formula
- Sort Columns in Excel without Mixing Data (3 Ways)
- How to Add Sort Button in Excel (7 Methods)
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.
⇰ 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.
⇰ 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 Create Custom Sort List 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-
=SORT(UNIQUE(B5:D14))
Here, B5:D14 is the dataset.
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.
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.
Read More: How to Sort Dates in Chronological Order in Excel (6 Easy Ways)
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.
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!
