How to extract unique values based on criteria in Excel?
Supposing, you have the left data range that you want to list only the unique names of column B based on a specific criterion of column A to get the result as below screenshot shown. How could you deal with this task in Excel quickly and easily?
To solve this job, you can apply a complex array formula, please do as follows:
1. Enter the below formula into a blank cell where you want to list the extracting result, in this example, I will put it to cell E2, and then press Shift + Ctrl + Enter keys to get the first unique value.
2. Then, drag the fill handle down to the cells until blank cells are displayed, and now all the unique values based on the specific criterion have been listed, see screenshot:
If you want to extract the unique values based on two conditions, here is another array formula can do you a favor, please do as this:
1. Enter the below formula into a blank cell where you want to list the unique values, in this example, I will put it to cell G2, and then press Shift + Ctrl + Enter keys to get the first unique value.
2. Then, drag the fill handle down to the cells until blank cells are displayed, and now all the unique values based on the specific two conditions have been listed, see screenshot:
Sometimes, you just want to extract the unique values from a list of cells, here, I will recommend a useful tool-Kutools for Excel, with its Extract cells with unique values (include the first duplicate) utility, you can quickly extract the unique values.
After installing Kutools for Excel, please do as this:
1. Click a cell where you want to output the result. (Note:Don't click a cell in the first row.)
2. Then click Kutools > Formula Helper > Formula Helper, see screenshot:
3. In the Formulas Helper dialog box, please do the following operations:
- Select Text option from the Formula Type drop down list;
- Then choose Extract cells with unique values (include the first duplicate) from the Choose a fromula list box;
- In the right Arguments input section, select a list of cells that you want to extract unique values.
4. Then click Ok button, the first result is displayed into the cell, then select the cell and drag the fill handle over to the cells that you want to list all the unique values until blank cells are shown, see screenshot:
More relative articles:
- Count The Number Of Unique And Distinct Values From A List
- Supposing, you have a long list of values with some duplicate items, now, you want to count the number of unique values (the values that appear in the list only once ) or distinct values (all different values in the list, it means unique values +1st duplicate values) in a column as left screenshot shown. This article, I will talk about how to deal with this job in Excel.
- Sum Unique Values Based On Criteria In Excel
- For example, I have a range of data which contains Name and Order columns, now, to sum only unique values in Order column based on the Name column as following screenshot shown. How to solve this task quickly and easily In Excel?
- Transpose Cells In One Column Based On Unique Values In Another Column
- Supposing, you have a range of data which contains two columns, now, you want to transpose cells in one column to horizontal rows based on unique values in another column to get the following result. Do you have any good ideas to solve this problem in Excel?
- Concatenate Unique Values In Excel
- If I have a long list of values which populated with some duplicate data, now, I want to find only the unique values and then concatenate them into a single cell. How could I deal with this problem quickly and easily in Excel?
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!