Nehad Ulfat
Extracting unique values based on single or different criteria is very common in offices & businesses. Microsoft Excel has also added a few useful & handy methods to extract unique values from a large dataset. In this article, I’ll try to illustrate how to extract unique values based on criteria in Excel with 2 effective methods.
Download Practice Workbook
You can download our Excel Workbook that we’ve used to prepare this article.
2 Effective Methods to Extract Unique Values Based on Criteria in Excel
For illustration, here is a sample dataset. Here, we have a chart of 5 Computer Shops. They have stocked new desktops and notebooks in the months of June and July in their shops.
Now, we will try to find unique products from this dataset based on several criteria.
1. Combine Excel UNIQUE & FILTER Functions to Extract Unique Values
In this first method, let us use the UNIQUE function and the FILTER function in excel to fetch out unique values. Here, we will use these functions for both single and multiple criteria. So without further delay, let’s jump into the methods.
1.1. Single Criteria
Here, we want to know which shops have stocked only notebooks, or only desktops, or both for 2 successive months in a year.
- Firstly, select cell E5 & type this formula
=UNIQUE(FILTER(C5:C14,D5:D14=E4))
- Secondly, press Enter & you will see the names of 4 computer shops that have stocked notebooks for over 2 months.
- Next, type this formula cell F5 to find out who has stocked desktops among those 5 shops.
=UNIQUE(FILTER(C5:C14,D5:D14=F4))- Then, press Enter & you will get the names of 3 shops that have stocked desktops over those months.
- You can also compare these two findings & you’ll notice that only Computer Sphere & EMACIMAC have stocked both types of devices.
Read More: Find Unique Values in a Column in Excel (6 Methods)
1.2. Multiple Criteria
Now we want to add one more criterion to the previous dataset. The computer shops have brought notebooks & desktops of 3 different brands- Lenovo, HP & Asus. And we’re going to find out which shops have stocked HP notebooks over those 2 months.
- First, select cell G12 where we want to see the names of shops that have stocked HP notebooks.
- Then, type this formula in that cell.
=UNIQUE(FILTER(C5:C14, (D5:D14=D5) * (E5:E14=E6)))- Afterward, press Enter.
- Finally, you’ll notice only 1 shop has stocked notebooks of HP over those 2 months.
1.3. Multiple Criteria with Alternatives
Now we’re going to deal with another condition where we want to find out which shops have stocked devices of at least one from HP or ASUS.
- First, select cell G11.
- Then, type this formula.
=UNIQUE(FILTER(C5:C14, (E5:E14=E6) + (E5:E14=E7)))- Following, press Enter.
- Finally, you’ll see the names of 4 shops that have stocked devices of either HP or ASUS.
2. Apply Array Formula to Pull Out Unique Values Based on Criteria in Excel
In this second method, we will use an array formula that you can use in any version of Microsoft Excel. Though you may find it a bit complex, I’ll try to explain later how this formula works for both single and multiple criteria.
2.1. Single Criteria
Now, let’s go to steps on how we can bring out the names of the shops that have stocked notebooks or desktops over 2 months with the help of array formulas.
- First, in cell E5 under the Notebook title, type this formula.
=IFERROR(INDEX($C$5:$C$14, MATCH(0, IF($E$4=$D$5:$D$14, COUNTIF($E$4:$E4, $C$5:$C$14), ""), 0)),"")
- Then, press Enter.
- Afterward, use the Fill Handle command to fill down the whole column & you’ll find the names of 4 computer shops that have stocked notebooks.
- Initially, the COUNTIF function ensures column E under the Notebook title that all the company names will appear here & thereby makes an array with a common 0 for all company names with multiple appearances.
- Outside this, the IF function now finds out which shops have stocked notebooks only. So, it removes 0 from the names of the shops that have not stocked notebooks.
- After that, the MATCH function searches for 0 only in the array found through the IF function previously.
- Now, the INDEX function stores all the cells in that array as a reference & shows the names of the shops only once those have appeared multiple times.
- Finally, the IFERROR function will remove all the error messages & replace them with empty strings.
Similarly, apply the array formula in cell F5 to find out the shop names that have Desktop in stock.
Read More: Excel VBA to Get Unique Values from Column (4 Examples)
2.2. Multiple Criteria
If we have to deal with two or more criteria while extracting unique values in Excel then here’s the solution for you. We are now going to find out the shops that have stocked notebooks of the HP brand only for over 2 months.
- First, select cell G12.
- Then, type this formula.
=IFERROR(INDEX($C$5:$C$14,MATCH(0,COUNTIF(G$11:$G11,$C$5:$C$14)+IF($D$5:$D$14<>$D$5,1,0)+IF($E$5:$E$14<>$E$6,1,0),0)),"")- Following, press Enter.
- Lastly, use Fill Handle to fill down the column until you’re shown blank cells at the bottom & you’re done.
- Here, the IF function is used twice. At first, searches for the Notebook category in column D & returns the results as 0 in the array.
- Similarly, searches for the HP brand in column E & return the results as 0 in another array.
- Then, the COUNTIF function here counts all the company names & will return the values as 0 in an array for all names found in column C under the Company title.
- Now, the MATCH function searches for the positions of 0 found as the resultant sum values alongside the last 3 arrays.
- Next, the INDEX function stores all this data as a reference array & shows the related names of the shops by the row positions of the resultant value 0 found in the preceding step.
- And last of all, the IFERROR function will remove all the error messages & display the shop names only.
Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
Conclusion
I hope the methods mentioned above on how to extract unique values based on criteria in excel will now help you to apply them in your Excel works and meet your requirements in analytical functions and data entries. You can also suggest to me in the comment box if you think I’ve missed a method that I should’ve mentioned in my article. Have a look at our other interesting & informative Excel articles on ExcelDemy.
