MD Tanvir Rahman
We live in an age of information revolution. Lots of data is taking place every day. The greatest challenge is to extract particular data for particular purposes. To extract unique values based on criteria in excel is our everyday routine. But not everyone’s cup of tea. In the article “Extract Unique Values Based on Criteria in Excel”, we tried to explain every possible way to extract data based on criteria.
📁 Download Excel File
To practice and get the overview, please download the excel file from here:
Learn to Extract Unique Values Based on Criteria in Excel with These 7 Approaches
In this article, we aim to demonstrate 7 quick approaches to extract unique values based on criteria in Excel. To extract unique values based on criteria in Excel, we take a dataset made on our own. There is a dataset of 5 columns and 13 rows containing First Name, Last Name, Category, and Age of some performers. We tried every possible way to figure out unique values.
1. Count and Extract Unique Values Based on Criteria in Excel
Generally, the UNIQUE function determines the repetitive value at once and it converts to a single value. The UNIQUE function can outline the desired value in both single row and multiple rows by selecting the data Range.
1.1 For Single Column
To determine the first name only once in a single column we used the UNIQUE function. To determine the values without repetition follow the below steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, we selected a cell, i.e. F6.
- In F6 we input a formula having a UNIQUE function with a range of data from B6 to B13.
- Now press Enter and get the unique values.
1.2 For Multiple Columns
To get UNIQUE values in multiple columns, you can also use a UNIQUE function. In this case, you have to select the entire column’s range to determine.
⬇️⬇️ STEPS ⬇️⬇️
- Similarly, first, select a blank cell where you want to get the value. I.e. cell E6.
- Then with the UNIQUE function input a formula and define the range B6 to C13.
- Now press Enter and get unique values of 2 columns.
2. Extract Unique Values Based on Criteria Using Logic
To determine unique values, logic along with the UNIQUE function can be used. Generally, logic is used under some specific criteria. Suppose, Someone tells you to get data that is only by once in the dataset or data which is more than once. In this case, UNIQUE function along with some mathematical logic can be an option to pull you out of the problem.
2.1 Values Occurring Once Only
To delineate data in a selected range only once in a dataset, set UNIQUE to TRUE in the third argument. Follow the below steps.
⬇️⬇️ STEPS ⬇️⬇️
- Select a cell i.e. E6.
- Insert the formula having UNIQUE to define a specific range B6 to B13.
- Now press Enter and get the unique value that is exactly once in a specified range.
2.2 Occurring More Than Once
To get data that is more than once in a specified range, the UNIQUE function along with FILTER and COUNTIF functions can be a way. COUNTIF is a statistical function that counts the cell number under a criterion. To filter data on some defined criteria under a specified range, the FILTER function is used.
⬇️⬇️ STEPS ⬇️⬇️
- Select a cell i.e. E6.
- Insert formula using logic, COUNTIF, FILTER, and UNIQUE function.
- Inserting the formula press Enter to get data more than once.
🔨 Formula Breakdown
FILTER Function filters out the repetitive data based on the number of occurrences with the help of COUNTIF function. Array results {2,2,2,1,1,1,2,1} (>1) Greater than one, is the comparison operation that changes the array into TRUE-FALSE, Where TRUE represents the multiplication of data. {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE} Array is filtered by the FILTER into {Lionel:Cristiano;Lionel;Cristiano} UNIQUE function eliminates the duplicate data and it results {Lionel; Cristiano}
3. Extract Unique Distinct From Multiple Columns and Gather Them into a Cell
Sometimes we have multiple columns of data but we need to use them in a cell. Like, Customer imputed their First name and Last name; On the other hand, a company requires their full name. This is how it can be done.
⬇️⬇️ STEPS ⬇️⬇️
- Select a cell i.e. E6.
- Using the UNIQUE function in the formula and select both columns’ ranges B6 to B13 and C6 to C13.
🔨 Formula Breakdown
Both B6:B13 and C6:C13 ranges match the full name. After that, the UNIQUE function eliminates duplication.
- Then press Enter and get the Unique values.
4. Distinct Value Formula with a Combination of SORT and UNIQUE Functions
As we show you earlier UNIQUE function eliminates duplication; On the other hand, the SORT function sorts the data under a certain criterion.
⬇️⬇️ STEPS ⬇️⬇️
- Select a blank cell i.e. E6.
- Insert the formula with the UNIQUE and SORT functions.
🔨 Formula Breakdown
- Press Enter and we obtain sorted data with unique data.
5. Extract Unique Values from Excel Column Using UNIQUE and FILTER Functions
The UNIQUE function and FILTER function also help to extract unique values based on criteria. As we dictated earlier in the article, the FILTER function helps to filter the data under a specific criterion whereas the UNIQUE function helps to eliminate repetition.
5.1 Single Criteria
To extract unique values under a criterion please check the below steps,
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a blank cell i.e. G6.
- Input the formula in G6.
- Now press Enter and get your desired data.
🔨 Formula Breakdown
Range D6:D13=D8 dictates that the criteria are Football and its range is D6 to D13. B6 to C13 is the range of player names. FILTER function filters the data from the specified area like {Lionel; Lionel; Robert; Erling; Cristiano} If you look closely here Lionel is 2 times. Now full function delivers to UNIQUE function that will eliminate duplication and the result is like, {Lionel; Robert; Erling; Cristiano}
5.2 Multiple Criteria
To obtain unique values under multiple criteria please have a look at the below steps,
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a blank cell i.e. G6.
- Insert the formula in G6 and in the formula.
- Here we choose (Football=D9) from D6:D13.
- As we choose age is less than 38, so we choose Range E6:E13 and logic is (E6:E13<E8).
- Now press Enter and get your desired data.
🔨 Formula Breakdown
Firstly, D6:D13=D9 dictates that the category is Football. Secondly, E6:E13<E8; Here E8 is 35 which means the age must be below 35. Thirdly, The formula now searches B6:C13 for the names who play football and whose age is below 35. It results like, {Robert; Erling} The FILTER function filters the data under the following criteria. Finally, the SORT function works to eliminate duplication. Though there is no duplication.
5.3 Multiple OR Criteria
OR criteria links between two or more criteria. Say, Find a group of people who plays football or modeling here comes OR logic.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a blank cell i.e. G6.
- Secondly, Insert the formula in G6 having OR logic.
- Thirdly, Here we choose (Football=D8) or (Modeling=B13) from D6:D13.
- Finally, Now press Enter and get your desired data.
🔨 Formula Breakdown
Firstly, D6:D13=D8 dictates that the category is Football. On the other hand, D13=Modeling. To link between the persons who play football or modeling here comes OR logic. Formula (D6:D13=D8)+(D6:D13=D13) . Thirdly, The formula now searches B6:C13 for the names who play football or do modeling. The FILTER function filters the data under the above criteria. It results in, {Lionel; Lionel Cristiano; Robert; Erling; Cristiano; David}. Finally, the SORT function works to eliminate duplication. Here Lionel and Cristiano are 2 times. Now it results like, {Lionel; Cristiano; Robert; Erling; David}.
5.4 Multiple And Criteria
AND criterion links between two or more criteria. Let’s say, Find a group of people who plays football and modeling; In this case, comes AND logic.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a blank cell i.e. G6.
- Insert the formula in G6 having AND logic.
- Here we choose (Football=D11) and age under 38 (E7=38) from D6:D13.
- Now press Enter and get your desired data.
🔨 Formula Breakdown
Firstly, D6:D13=D11 dictates that the category is Football. On the other hand, E7=38. To link between the persons who play football and are aged below 38 here comes OR logic. Formula (D6:D13=D11)+(E6:E13<E7) . Thirdly, The formula now searches B6:C13 for the names who play football and those ages below 38. The FILTER function filters the data under the above criteria. It results, {Lionel; Lionel; Robert; Erling}. Finally, the SORT function works to eliminate duplication. Here Lionel is 2 times. Now it results like, {Lionel; Robert; Erling}.
6. Extract List Based on Criteria Using UNIQUE and CHOOSE Function in Excel
UNIQUE and CHOOSE functions also use in some cases to relate the data sometimes. CHOOSE function is a 2-dimensional array of values that returns to columns from specified columns.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a blank cell i.e. G6.
- Insert the formula in G6 having CHOOSE and UNIQUE functions.
- Here we choose D6:D13 for the category and B6:B13 for performers.
- Now press Enter and get your desired data.
🔨 Formula Breakdown
Range D6:D13 to choose from the category and range B6:B13 to choose the names. As CHOOSE function is a two-dimensional array, it works with two new specific columns. It results {Football, Lionel; Football, Lionel; Modeling, Cristiano; Football, Robert; Cricket, Ricky; Football, Earling; Football, Cristiano; Modeling, David} Finally, SORT function works to eliminate duplication. Here Lionel is 2 times who plays Football. Now it results like, {Football, Lionel; Modeling, Cristiano; Football, Robert; Cricket, Ricky; Football, Earling; Football, Cristiano; Modeling, David}.
7. Use of Array to Extract Dynamic List Based on Criteria
The use of an array may also help to extract unique values based on criteria in Excel. Though the use of an array may be a lengthy and complex process. But we will try to explain how the process is done.
7.1. For Single Criteria
Let’s use an array for a single criterion. Please follow the below procedure.
⬇️⬇️ STEPS ⬇️⬇️
- Select a blank cell i.e. G6 under the Football title.
- Then insert the following formula containing IFERROR, INDEX, IF, and COUNTIF functions.
- Press Enter to get desired value and it is only Messi and Ronaldo respectively who plays Football and do Modeling.
🔨 Formula Breakdown
Firstly, the COUNTIF function confirms column-G under the Football title and an array for all names that make common 0. IF function finds the names that play Football and removes 0 that don’t. Meanwhile, through the IF function, the array MATCH function searches only 0. Next, as a reference array from column C, the INDEX function relates and stores all names by the row of 0 resultant value. Finally, the IFERROR function removes every error message and shows performer names.
7.2. For Multiple Criteria
To deal with two or more criteria, you can also use an array. Here we are going to try out to get names who is not only a footballer but also 35 years old.
⬇️⬇️ STEPS ⬇️⬇️
- Select a blank cell i.e. G6.
- Insert the array formula in G6.
- Afterward, press Enter and we get Messi who plays Football and is 35 years old.
🔨 Formula Breakdown
First, we used the IF function twice. We search Football in column D and at an array, it returns 0 as result. Secondly, in column E we search 38 (age) and in an array, it returns 0. Thirdly, the COUNTIF function counts all the names in column C, and an array returns 0. Then, the MATCH function searches to pick up 0, the resultant sum. Next, as a reference array from column C, the INDEX function relates and stores all names by the row of 0 resultant value. Finally, the IFERROR function removes every error message and shows performer names.
📄 Important Notes
🖊️ We used UNIQUE, FILTER, COUNTIF, INDEX, SORT, IF, and IFERROR functions to describe various approaches to extract unique values based on criteria in Excel.
🖊️ The SORT function is only available on MS OFFICE 2022 version. In the older versions, it is unavailable. So, be careful when you use the SORT function.
🖊️ Concentrate while using an array, otherwise, it may lead you to the wrong values.
📝 Takeaway from This Article
📌 I hope you can extract any type of unique value using this article’s approaches.
📌 We also tried to demonstrate the use of SORT, UNIQUE, FILTER, COUNTIF, INDEX, IFERROR, and IF functions.
📌 Use of the array can be your key to learning from the article.
Conclusion
We believe the approaches mentioned earlier will help you in every way while working with Excel. Also, we tried our best to delineate every possible way to extract unique values based on criteria in Excel. Please comment below if you have further queries; Also any suggestions are appreciated and for more articles please visit, www.ExcelDen.com.
