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

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
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

Filter unique distinct records with a condition
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 Comment