Lamisa Musharrat
Maybe you have a list of repeating data that you want to sort. In Excel, you can actually make a unique list based on criteria. However, this is only possible if you use the Excel 365 version. Because the unique function that we are going to use to create a unique list in Excel based on criteria is not available in other versions of Excel. This article is going to discuss how to use the unique function to create a unique list in Excel based on criteria.
📁 Download Excel File
Download the Excel file we used to create this article so you can practice.
Learn to Create a Unique List in Excel Based on Criteria with These 8 Suitable Approaches
Assume a company has a dataset of employees of various ages working in various departments. Now we are going to set different criteria and create a unique list with nine different approaches. Follow the rest of the article to learn more.
1. Creating a Unique List Sorted in Alphabetical Order
You want to sort your data in alphabetical order while creating a unique list. You can do this by using the SORT function.
In Excel. The SORT function allows you to arrange data in ascending (A-Z) or descending (Z-A) order. Follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell to see your unique sorted list. We have selected cell F6.
- Then, Enter the following formula.
=SORT(UNIQUE(B6:C11,FALSE,FALSE),,1)
🔨 Formula Breakdown
SORT(UNIQUE(B6:C11,FALSE,FALSE),,1)
👉 Here, B6:C11 is used as the array of the list and UNIQUE(B6:C11,FALSE,FALSE) returns the unique values from the list which is (Frank,Mellisa,Thomas,Thomas). Notice that though Thomas is two times, it’s because this value has two different values as age, which means both are not the same.
👉 Therefore,SORT(UNIQUE(B6:C11,FALSE,FALSE),,1) becomes SORT(Frank,Mellisa,Thomas,Thomas,,1) which then sort the data by ascending order.The space between two commas is for the sort_order argument and 1 is for by_order.
- This function will return an array that is sorted in alphabetical order. Note that, this data is sorted in ascending order (A-Z).
2. Generating a Unique List with Multiple Columns Criteria
In this approach, we will show you how to use only the UNIQUE function to create a unique list. You will generate a multiple-column, unique list using the formula that we are going to mention here. So, let’s see the necessary steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, specify a cell where you are going to create your list. We have used cell F6.
- After that, type the following formula in that cell.
=UNIQUE(B6:C11,FALSE,FALSE)
In this formula,
B6:C11 is the range for the first two columns of the list.
- Press Enter from your keyboard, and a unique list will appear.
3. Integrating IFERROR, FILTER, and UNIQUE Functions
Suppose you have set criteria and want to create a list based on that. So, in this case, you can use the IFERROR function combined with the function UNIQUE. The advantage of this approach is that you can customize the error message if the data you are looking for is not available in that list. Let’s see how to do this with the following steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select the criteria to make the list based on that. We have taken the Public relation department and age less than or equal to 27 as our criteria to search for the names of the employees.
- After that, select a cell where you want to create the list. We have taken cell B17.
- Then insert the following formula.
=IFERROR(UNIQUE(FILTER(B6:C11,C6:C11<=C14)), “Not Found”)
🔨 Formula Breakdown
IFERROR(UNIQUE(FILTER(B6:C11,C6:C11<=C14)), “Not Found”)
👉 FILTER(B6:C11,C6:C11<=C14) filter the range of the data B6:C11 based on the criteria, and the criteria are defined in the cell C14 and B14. And, C6:C11 is the range where this function will look for the criteria. So, it will eventually search for data in the Age column and filter the data which is less than or equal to 27. So, it gives the output (0), as there is no such data which is in public relations and also has less than or equal to 27 age.
👉 So, UNIQUE(FILTER(B6:C11,C6:C11<=C14)), becomes UNIQUE(0).
👉 After that the rest of the IFERROR function will return “Not Found” as it receives an error message due to the absence of the required data, “Not Found”.
- Now, press the Enter key and you will find that your list is created. If the data you are looking for is not absent from the dataset then it will show the message “Not Found”.
4. Combining INDEX and MATCH Functions
You can create a unique list using the INDEX and MATCH functions. If you aren’t able to use the UNIQUE function in other versions of Excel, this method is for you. Let’s see how to do this.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, specify the criteria in a fixed cell for later use, We have taken the cell F6 for this purpose.
- Secondly, write the following formula in that cell.
=INDEX(B6:B11, MATCH(0, IF($F$6=$D$6:$D$11,COUNTIF($F$5:$F5, $B$6:$B$11), “”), 0))
🔨 Formula Breakdown
INDEX(B6:B11, MATCH(0, IF($F$6=$D$6:$D$11,COUNTIF($F$5:$F5, $B$6:$B$11), “”), 0))
👉 Firstly,the COUNTIF($F$5:$F5, $B$6:$B$11), test the criteria stored in F5 and then it counts data in the range B6 to B11, so it returns B8 and B10.
👉 Then, IF($F$6=$D$6:$D$11,COUNTIF($F$5:$F5, $B$6:$B$11), “”) becomes IF($F$6=$D$6:$D$11,B8,B10 “”).This function illustrates that if F6 which is the criteria is equal to any data within the range of D6 to D11, then return Thomas otherwise keep it empty.
👉 So the function MATCH(0, IF($F$6=$D$6:$D$11,COUNTIF($F$5:$F5, $B$6:$B$11), “”), 0) becomes MATCH(0,B8:B10, 0) searches for the B8 and B10 in the cells and returns the value 8 and 2 as the row number and column number.
👉 Finally,INDEX(B6:B11, MATCH(0, IF($F$6=$D$6:$D$11,COUNTIF($F$5:$F5, $B$6:$B$11), “”), 0)) becomes INDEX(B6:B11,8,2) and returns the Thomas in the list.
- Thirdly, enter the formula using the Enter button.
- Now, you have to use the Fill Handle icon to obtain the other value. Here we have dragged the Fill Handle icon rightwards and got the value for the age.
- See that the adjacent cell is filled by the Fill Handle icon.
5. Constructing a Unique List of Multiple Columns
Suppose, we want to make a list that will contain values from multiple unique columns from the dataset. In this case, we have to use the CHOOSE function to do so, along with the UNIQUE function. Follow the steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, specify a cell where you are going to create your list. We have used the cell.
- After that, type the formula below in that cell.
=UNIQUE(CHOOSE({1,3},B6:B11,C6:C11,D6:D11))
🔨 Formula Breakdown
UNIQUE(CHOOSE({1,3},B6:B11,C6:C11,D6:D11))
👉 Here,B6:B11,C6:C11 and D6:D11 are ranges to look for data and the CHOOSE function returns values of the three-dimensional array, such as {“Frank”,”28″; “Salesl”,”Melissa”; “30”,”Human resources”; “Thomas”,”33″; “Public Relation”,”Frank”; “28”,”Finance”; “Thomasl”,”40″; “Public Relation”,”Melissa”; “30”,”Human Resource”}.
👉 Then from this list, the UNIQUE function creates a list where all the data are unique.
- Finally, Enter the formula with the help of your keyboard. You can see that you have created a unique list with the use of the data from two different columns.
6. Using UNIQUE and FILTER Functions
Now we will see how to make a unique list for unique conditions. We will be using the function FILTER integrating with the UNIQUE function to generate a list for multiple criteria. Let’s follow the below steps to learn about it.
6.1 Ignoring Blank Cells
Suppose in your dataset you got some blank cells. In this condition, if you run the unique function it will show you an error. So, you have to customize your function. Follow the steps mentioned below to create a unique list in excel based on the criteria.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select the cell where you want to generate the new unique list. We have selected cell F6.
- After that Enter the following formula in that cell.
=UNIQUE(FILTER(B6:C11,D6:D11<>””))
🔨 Formula Breakdown
UNIQUE(FILTER(B6:C11,D6:D11<>””))
👉 Here, the FILTER function filters all the data in the range of B6:C11 and D6:D11 and filters all the empty strings and blank cells by the symbol <> “”. As a result, FILTER(B6:C11,D6:D11<>””) returns values stored in non-empty cells.
👉 After that, the UNIQUE function search up the data fully and finally returns the values that are unique.
- Now, you can see that a list is created where all blank cells are gone only containing the unique names of the employee.
6.2 Identifying Multiple OR Criteria
Let’s assume that we have set two criteria that are in the same column. For example, we have set the criteria as public relations and human resources both of which are in the department column. Now we want to generate a list of these two department’s employees.
Let’s see how to do this.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, enter your desired criteria in a different cell in any part of your worksheet. You can see that we have used cells F6 and F7.
- Secondly, specify a cell where you are going to create your list. We have used cell F6.
- Then, insert the formula below in that cell.
=UNIQUE(FILTER(B6:C11,(D6:D11=F6)+(D6:D11=F7)))
🔨 Formula Breakdown
UNIQUE(FILTER(B6:C11,(D6:D11=F6)+(D6:D11=F7)))
👉 In this formula, + represents the OR criteria.So, in FILTER(B6:C11,(D6:D11=F6)+(D6:D11=F7)) ,D6:D11 is criteria range 1 and F6 is the criteria also , F7 is the second criteria . So this function checks for the criteria in these specified ranges, and returns for each array TRUE or FALSE.
👉 Any data that meets the condition makes it into the array that is then handed over to the UNIQUE function.
👉 Finally, this entire function returns those unique value that meets up the criteria.
- Finally, enter the formula with the Enter key from your keyboard and you will see the unique list in your desired location.
6.3 Searching on Multiple AND criteria
Suppose, we have two different criteria sets that belong to two different columns. Now, we want to create a list based on these criteria. So, follow the steps below to create a unique list in excel based on the criteria.
⬇️⬇️ STEPS ⬇️⬇️
- First, enter the two criteria in two cells of your datasheet to use in your formula. We have used cells F6 and G6 to insert those criteria.
- After that, select the cell where you want to generate the new unique list. We have the selected cell.
- Now, insert the formula below, to create the list.
=UNIQUE(FILTER(B6:C11,(D6:D11=F6)*(C6:C11<G6)))
🔨 Formula Breakdown
UNIQUE(FILTER(B6:C11,(D6:D11=F6)*(C6:C11<G6)))
👉 Firstly, the operations inside the brackets are executed. (D6:D11=F6) which indicates the value in the range of the department column is equal to the criteria stored in the F6 cell. It gives two values for the criteria. Also (C6:C11<G6) returns one value that is less than 35 years old in the range of age.
👉 So, FILTER(B6:C11,(D6:D11=F6)*(C6:C11<G6)) firstly finds out if the criteria are matched and return a list of value matching the criteria. Here it will return, {“Thomas”,”33″; “Public Relation”}.
👉 Finally, UNIQUE(FILTER(B6:C11,(D6:D11=F6)*(C6:C11<G6))) makes a unique list containing the values returned by the filter function and gives the output.
- Now from the Enter key of your keyboard enter the formula. The list according to the criteria is created.
7. Generating a Unique List of Multiple Criteria
Suppose you want to create a unique list in excel based on multiple criteria. To do that, you can use multiple functions in your formula. Follow the process below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, insert your criteria in specific cells. Here we have used the cells B14 and C14.
- After that, specify a cell where you are going to create your list. We have used cell B17.
- Then, insert the following formula in that cell.
=IFERROR(INDEX($B$6:$B$11,MATCH(0,COUNTIF(B16:$B$16,$B$6:$B$11)+IF(D6:D11=$B$14,1,0)+IF(C6:C11<$C$14,1,0),0)),””)
🔨 Formula Breakdown
IFERROR(INDEX($B$6:$B$11,MATCH(0,COUNTIF(B16:$B$16,$B$6:$B$11)+IF(D6:D11=$B$14,1,0)+IF(C6:C11<$C$14,1,0),0)),””)
👉 Here, the IF functions are used to make logical comparisons. So in the range D6:D11 or C6:C11 it searches for the criteria stored in B14 and C14 respectively and runs the logic defined by the signs = or < and if the logic is TRUE then returns 1 otherwise returns 0.
👉 The COUNTIF function counts the cells mentioned by the range B6:B11 according to the criteria set by the IF function.
👉 For the MATCH function, 0 represents the lookup_value. And this function searches for the species item mentioned by the criteria and returns for the relative position of that item in the range.
👉 INDEX function is an array formula and $B$6:$B$11 represents the array.
And the rest of the functions nested in it represents the row_num and column_num.
👉 And finally, the IFERROR function is used to avoid any kind of error message if the value according to the criteria is absent. Then it will return the value 0.
- Finally, Enter the formula and see that the unique list matching your criteria is created.
8. Making a Unique List Concatenated on One Cell
Let’s say we want to take data from two different columns and make a unique list of one column containing. This is a very simple task to do and you have to use only one function which is the UNIQUE function. Let’s do it following the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, specify a cell where you are going to create your list. We have used cell F6.
- Secondly, just insert the formula below in that cell.
=UNIQUE(B6:B11&”, “&C6:C11)
- Finally, press Enter from your keyboard and you will see that a unique list is created with the values of both columns joined or concatenated.
📝 Takeaway from This Article
📌 You have learned how to create a unique list using Microsoft Excel 365 function UNIQUE.
📌 We have also shown how you can do this by using an alternative to the UNIQUE function.
Conclusion
It may be a gruesome process to process the data and select the unique data manually. Excel functions can be a rescue for you in this case. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.
