How to Extract Unique Values Based on Criteria in Excel

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.

Dataset to extract unique values based on criteria.

Method

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.

Count and Extract

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.
=UNIQUE(B6:B13)
  • Now press Enter and get the unique values.

Use of basic UNIQUE functions to extract unique values based on criteria.

Count and Extract

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.

=UNIQUE(B6:C13)
  • Now press Enter and get unique values of 2 columns.

Use of basic UNIQUE function to extract unique values based on criteria in multiple columns.

Method

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.

Use of Logic

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.

=UNIQUE(B6:B13,,TRUE)
  • Now press Enter and get the unique value that is exactly once in a specified range.

Use of UNIQUE function to extract unique values based on criteria.

Use of Logic

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.
=UNIQUE(FILTER(B6:B13, COUNTIF(B6:B13, B6:B13)>1))
  • Inserting the formula press Enter to get data more than once.

Use of basic UNIQUE and FILTER function and logic to extract unique values

πŸ”¨ 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}

Method

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.
=UNIQUE(B6:B13&” β€œ&C6: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.

Extract unique values from multiple column

Method

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.

=SORT(UNIQUE(B6:D13))

πŸ”¨ Formula Breakdown

  • Firstly Range B6:D13 eliminates duplication by applying a UNIQUE function.
  • After that, SORT combining the UNIQUE function dictates the unique values of each segment.

  • Press Enter and we obtain sorted data with unique data.

SORT and UNIQUE functions to extract unique values based on criteria

Method

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.

Use of UNIQUE and FILTER Function

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.
=UNIQUE(FILTER(B6:C13, D6:D13=D8))
  • Now press Enter and get your desired data.

FILTER and UNIQUE functions to extract unique values based on criteria.

πŸ”¨ 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}

Use of UNIQUE and FILTER Function

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

=UNIQUE(FILTER(B6:C13, (D6:D13=D9) * (E6:E13<E8)))
  • Now press Enter and get your desired data.

UNIQUE, FILTER, OR logic to extract unique values

πŸ”¨ 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.

Use of UNIQUE and FILTER Function

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.
=UNIQUE(FILTER(B6:C13,(D6:D13=D11)+(E6:E13<E7)))

UNIQUE, FILTER, OR logic

πŸ”¨ 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}.

Use of UNIQUE and FILTER Function

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.
=UNIQUE(FILTER(B6:C13,(D6:D13=D11)*(E6:E13<E7)))
  • Now press Enter and get your desired data.

UNIQUE, FILTER, AND logic to extract unique values

πŸ”¨ 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}.

Method

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.
=UNIQUE(CHOOSE({1,2},D6:D13, B6:B13))
  • Now press Enter and get your desired data.

UNIQUE and CHOOSE functions to extract unique values

πŸ”¨ 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}.

Method

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.

Use of Array

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.
=IFERROR(INDEX($C$6:$C$13, MATCH(0, IF($G$5=$D$6:$D$13, COUNTIF($G$5:$G5, $C$6:$C$13), β€œβ€), 0)),””)
  • Press Enter to get desired value and it is onlyΒ  Messi and Ronaldo respectively who plays Football and do Modeling.

extract unique values based on criteria in excel.

πŸ”¨ 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.

Use of Array

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.
=IFERROR(INDEX($C$6:$C$13,MATCH(0,COUNTIF(G$5:$G5,$C$6:$C$13)+IF($D$6:$D$13<>$D$9,1,0)+IF($E$6:$E$13<>$E$8,1,0),0)),””)
  • Afterward, press Enter and we get Messi who plays Football and is 35 years old.

Use of an array to extract unique values based on multiple criteria.

πŸ”¨ 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.

(Visited 76 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo