Filter unique distinct records with a condition
If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a unique distinct list based on each condition.
I remember reading that Excel has difficulty with these type of or conditions in arrays.
Answer:
Array formula in cell C19:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy array formula
- Select cell C19
- Copy cell (Ctrl + c)
- Select cell range C19:D22
- Paste (Ctrl + v)
How the array formula in cell C19 works
Step 1 - Identify unique distinct records
COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)
becomes
COUNTIFS("Category", {"Coffee";"Coffee";"Coffee";"Coffee";"tea";"juice";"tea"}, "Item", {"Espresso";"Espresso";"Americano";"Americano";"Americano";"Florida";"English"},)
and returns {0;0;0;0;0;0;0}
How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.
How to use the COUNTIFS function
Step 2 - Filter records with condition
COUNTIF($C$13:$C$14, $C$5:$C$11)=0
becomes
COUNTIF({"Coffee"; "tea"}, {"Coffee"; "Coffee"; "Coffee"; "Coffee"; "tea"; "juice"; "tea"})=0
and returns {FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
How to use the COUNTIF function
Step 3 - Match filtered records
MATCH(0, COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=0), 0)
becomes
MATCH(0, {0;0;0;0;0;0;0}+{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, 0)
becomes
MATCH(0, {0;0;0;0;0;1;0},0)
and returns 1.
Identify the position of a value in an array.
Step 4 - Return a value or reference of the cell at the intersection of a particular row and column
INDEX($C$5:$D$11, MATCH(0, COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=0), 0), COLUMN(A1))
becomes
INDEX({"Coffee", "Espresso";"Coffee", "Espresso";"Coffee", "Americano";"Coffee", "Americano";"tea", "Americano";"juice", "Florida";"tea", "English"}, 1, 1)
and returns "Coffee" in cell C19.
Gets a value in a specific cell range based on a row and column number.
Tip! Did you know that a pivot table can easily extract unique distinct records too?
How to use Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
How to use Pivot Tables – Excel’s most powerful feature and also least known
How to remove errors
IFERROR(value, value_if_error) returns value_if_error if expression is an error and the value of the expression itself otherwise
The array formula becomes:
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
Count unique distinct records with a date and column criteria
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Unique distinct records sorted based on count or frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Extract unique distinct records from two data sets
The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]
Extract unique distinct records based on a criterion
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
Filter unique distinct records using criteria
This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]
3 Responses to “Filter unique distinct records with a condition”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar, this works great. Keep up the great work.
When I tried the workbook that was uploaded to this post-reply and pressed with left mouse button on the formula bar for cell C19 and hit 'ENTER' (without actually changing anything) and I get an #N/A record.
I'm running excel 2010, so I can only assume that the code in cell C19 'may' not work in 2010? Could this be possible? If so, can you help me with code that would work in Excel 2010??
Carlos,
When I tried the workbook that was uploaded to this post-reply and pressed with left mouse button on the formula bar for cell C19 and hit 'ENTER' (without actually changing anything) and I get an #N/A record.
That is one of the disadvantages with array formulas. If you edit an array formula (even though you don´t do any changes) you must enter it as an array formula. See: How to create an array formula, above.
I'm running excel 2010, so I can only assume that the code in cell C19 'may' not work in 2010? Could this be possible? If so, can you help me with code that would work in Excel 2010??
It works in excel 2010.