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 values from several columns.
In this article, I want to show you how to narrow that search down a bit further. 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 column C containing value 11.
Array formula in G6:
To enter an array formula, type the formula in cell G6 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, they appear automatically.
Explaining formula in cell G6
First, let me explain what a unique distinct record is. A record is an entire row in the table, in this example. The picture below displays a small table in column B and C containing a duplicate record. The table in column E and F contains only unique distinct records.
In other words, unique distinct records are all records but duplicate records are removed. The record in cell range B4:C4Â is removed.
Step 1 - Check if records have been displayed
The COUNTIFS function lets you count values combined which is perfect when it comes to counting data records. The following part of the formula checks if previous records in the list has been displayed, if a record has been shown the corresponding value in the array returns 1 and if not 0 (zero).
COUNTIFS($G$5:$G5, $B$4:$B$27, $H$5:$H5, $C$4:$C$27, $I$5:$I5, $D$4:$D$27, $J$5:$J5, $E$4:$E$27)
returns {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.
This means that no record has been displayed yet in the list, remember that we are checking the formula in cell G6.
Step 2 - Make sure that only records with the condition met are filtered
The COUNTIF function lets you build an array that indicates which records meet the condition.
COUNTIF($H$2,$D$4:$D$27)=0
becomes
{1;1;1;0;0;0;1;1;0;1;1;0;1;0;1;1;0;1;1;0;0;0;0;1}=0
and returns {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE}.
Step 3 - Add arrays
COUNTIFS($G$5:$G5, $B$4:$B$27, $H$5:$H5, $C$4:$C$27, $I$5:$I5, $D$4:$D$27, $J$5:$J5, $E$4:$E$27)+(COUNTIF($H$2,$D$4:$D$27)=0)
becomes
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0} +Â {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE}
and returns {0;0;0;1;1;1;0;0;1;0;0;1;0;1;0;0;1;0;0;1;1;1;1;0}.
Step 4 - Find the position of the first 0 (zero) in the array
To be able to get the record we need the formula needs to know where the first record is that not yet has been shown. The MATCH function lets you find the position of the first 0 (zero) in the array.
MATCH(0, COUNTIFS($G$5:$G5, $B$4:$B$27, $H$5:$H5, $C$4:$C$27, $I$5:$I5, $D$4:$D$27, $J$5:$J5, $E$4:$E$27)+(COUNTIF($H$2,$D$4:$D$27)=0), 0)
becomes
MATCH(0, {0;0;0;1;1;1;0;0;1;0;0;1;0;1;0;0;1;0;0;1;1;1;1;0}, 0)
and returns 1.
Step 5 - Return the first value from the first record
The INDEX function lets you get a value from the worksheet based on a row number and column number.
INDEX($B$4:$E$27, MATCH(0, COUNTIFS($G$5:$G5, $B$4:$B$27, $H$5:$H5, $C$4:$C$27, $I$5:$I5, $D$4:$D$27, $J$5:$J5, $E$4:$E$27)+(COUNTIF($H$2,$D$4:$D$27)=0), 0), COLUMN(A1))
becomes
INDEX($B$4:$E$27, 1, COLUMN(A1))
becomes
INDEX($B$4:$E$27, 1, 1)
and returns "Sample0" in cell G6.
Get Excel *.xlsx file
Extract unique distinct records based on a condition.xlsx
Unique distinct records category
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Excel categories
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.