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.

**Approach**

### 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).

**Approach**

### 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.

**Approach**

### 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**”.

**Approach**

### 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.

**Approach**

### 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.

**Approach**

### 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.

**Approach 6.1**

#### 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.

**Approach 6.2**

#### 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.

**Approach 6.3**

#### 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.

**Approach**

### 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.

**Approach**

### 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.