Return unique rows based on certain criteria

how to extract unique values based on multiple criteria in excel
excel list unique values based on criteria in another column
excel dynamic list based on multiple criteria
excel extract list based on criteria
excel create list based on multiple criteria
vlookup unique values
excel unique values formula
how to extract unique values based on criteria in excel vba

I need a query that will return distinct rows, but will not return any rows that have duplicate fruits where one of the rows is 'Approved'. Note: there will always be at most two fruits of the same name.

For example, the ideal result would be:

What I have so far is simply:

SELECT DISTINCT * FROM Food;

But this obviously still returns one of the rows that has the fruit 'Apple'

You can make use of some conditional aggregation for the desired result set like

select fruit, min(status)
from demo
group by fruit
having sum(status = 'Approved') = 0
and sum(status = 'Bought') > 0

or just use select fruit, 'Bought' as status

Demo

How to extract unique values based on criteria in Excel?, Excel formula: Unique values with criteria which returns the 5 unique values in group A, as seen in E5:E9. To extract only unique values from a list or column, you can use an array formula based on INDEX, MATCH, and COUNTIF. And you want to extract a unique product list from this range and return non-duplicates that are unique based on the month value. For example, you want to extract a product name list based on Jan month. You can use an Excel array formula based on the INDEX function, the MATCH function, the IF function, and the COUNTIF function to achieve the result. Here is the formula that it will extract the unique distinct product name list based on month value:

SELECT DISTINCT * FROM Food WHERE Fruit NOT IN (SELECT (UNIQUE Fruit) FROM Food WHERE Status = 'Approved');

Try this query.

Excel formula: Unique values with criteria, How to create a unique distinct list based on two conditions Step 2 - Create boolean array with values indicating first criterion Step 5 - Return a value or reference of the cell at the intersection of a particular row and column. Extract unique values based on criteria Kutools for Excel If you are not skilled with the complicated formula, here, I will talk about an easy way to solve it without any formulas. You can filter the data by a specific criterion firstly, and then apply the Select Duplicates & Unique Cells feature of Kutools for Excel to select the unique values and then paste them to other cells you need.

I think you could solve this problem a different way perhaps? To get that same result you could add a WHERE clause, for instance:

SELECT DISTINCT * FROM Food WHERE Status = 'Bought';

You could also alter the data model so that a fruit's status is updated rather than adding another record for the same fruit (if that suits the application's purposes).

If you want to maintain the schema and get the same, I believe you can do: SELECT DISTINCT on (Fruit) Fruit, Status FROM Food ORDER BY Fruit, Status ASC;

Source

How to create a unique distinct list based on two , This time I want to search for unique distinct records based on a condition that must match. I want to extract for unique distinct records with  Basically, the INDEX () function will look at a table (the array ), then based on the ROW and COLUMN you give it, a single value will be returned. This single value will be one of the items in our unique list. The secret here is to manipulate the ROW portion, which we will dig into shortly.

Extract unique distinct records based on a criterion, See how to get unique values in Excel with formulas. COUNTIF(range, criteria) returns the number of cells within a range that meet a specified condition. array based on the specified row and (optionally) column numbers. MATCH finds the row number for which all the criteria are TRUE (more precisely, the the relative position of that row in the specified array) and passes that number to the row_num argument of the first INDEX: =INDEX(D2:D13, 3) INDEX MATCH with multiple criteria in rows and columns

Get a list of distinct / unique values in Excel & extract unique rows, Need to create a unique list in Excel, but don't know how to create the formula? to see how people create a unique list in Excel based on a specific condition. based on the ROW and COLUMN you give it, a single value will be returned. Extract all rows from a range that meet criteria in one column [Advanced Filter] The image above shows a filtered dataset in cell range B5:E15 using Advanced Filter which is a powerful feature in Excel. Here is how to apply a filter: Create headers for the column you want to filter, preferably above or below your data set.

Create a Unique List in Excel based on Criteria, I'm not 100% sure I understand the question as it seems to me that the first John Doe should be in the result in that he is a unique owner with a  Extract rows that meet criteria with Filter function. In Excel, you can filter the rows meeting the criteria first, and then copy them to another location. 1. Select the range included headers you want to extract rows from, click Data > Filter to add the Filter icons beside headers. See screenshot: 2.

Comments
  • Can be done in several ways. Start with a NOT EXISTS query, try GROUP BY with HAVING etc.
  • See meta.stackoverflow.com/questions/333952/…
  • This is gibberish
  • Distinct star is completely nonsensical
  • It's not gibberish because rows can be identical, not all tables have unique primary keys. Such as in OP's case.
  • By definition, ALL tables have PRIMARY KEYs. If they don't, then they're not tables. But this is a red herring