Filter unique distinct records

1. How do I extract unique distinct rows?

First, let me explain unique distinct records. A record is an entire row in the table, in this example. The picture below displays a small table in columns B and C containing a duplicate record. The table in columns E and F contains unique distinct records only.

In other words, unique distinct records are all records but duplicate records are removed.

Second, I highly recommend using a pivot table to extract unique distinct records if you are working with a really large data set. A Pivot table is incredibly fast and is also easy to quickly set up and manage.

Third, this post shows you how to construct an array formula that extracts unique distinct records.

Update 10 December 2020, Excel 365 formula:

This is a much smaller regular formula, you can read more about the UNIQUE function here: Extract unique distinct rows

Cell range A3:D26 contains the original list, cell range F3:I16 contains the extracted list. The following formula works for Excel versions prior to Excel 365.

Array formula in cell F3:

Back to top

How to enter an array formula

1. Select cell F3
2. Type above formula in cell or formula bar
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter once

If you did the steps above correctly, the formula has now a beginning and ending curly bracket, like this {=array_formula}
Don't enter these characters yourself, they appear automatically if you did this right.

Back to top

How to copy formula

Copy cell F3 and paste it to cells to the right, as far as needed. Then copy cells and paste them down, as far as needed.

Back to top

Explaining array formula in cell F3

You can easily follow along while I go through this array formula, go to tab "Formulas" on the ribbon. Select cell F3 then press with left mouse button on "Evaluate Formula" button. Press with left mouse button on "Evaluate" button to move to next step.

Step 1 - Identify rows with unique records

The COUNTIFS function counts the number of cells specified by a given set of conditions or criteria

COUNTIFS($F$2:$F2, $A$3:$A$26, $G$2:$G2, $B$3:$B$26, $H$2:$H2, $C$3:$C$26, $I$2:$I2, $D$3:$D$26)

becomes

COUNTIFS($A$28:$A28, {"Sample0"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"}, $B$28:$B28, {"B";"B";"A";"A";"B";"B"; "B";"A";"A";"A";"A";"A";"B";"A";"B"; "B";"A";"A";"B";"B";"A";"A";"A";"A"}, $C$28:$C28, {11; 11; 11; 10; 10; 10; 11; 11; 10; 11; 11; 10; 11; 10; 11; 11; 10; 11; 11; 10; 10; 10; 10; 11}, $D$28:$D28, {"AA111"; "AA110"; "AA111"; "AA111"; "AA110"; "AA111"; "AA111"; "AA110"; "AA110"; "AA110"; "AA111"; "AA110"; "AA110"; "AA111"; "AA111"; "AA111"; "AA110"; "AA110"; "AA110"; "AA111"; "AA110"; "AA110"; "AA110"; "AA110"})

becomes

COUNTIFS("Sample", {"Sample0"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"}, "Group", {"B";"B";"A";"A";"B";"B"; "B";"A";"A";"A";"A";"A";"B";"A";"B"; "B";"A";"A";"B";"B";"A";"A";"A";"A"}, "Number", {11; 11; 11; 10; 10; 10; 11; 11; 10; 11; 11; 10; 11; 10; 11; 11; 10; 11; 11; 10; 10; 10; 10; 11}, "Category", {"AA111"; "AA110"; "AA111"; "AA111"; "AA110"; "AA111"; "AA111"; "AA110"; "AA110"; "AA110"; "AA111"; "AA110"; "AA110"; "AA111"; "AA111"; "AA111"; "AA110"; "AA110"; "AA110"; "AA111"; "AA110"; "AA110"; "AA110"; "AA110"})

and 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}

Step 2 - Find relative position of  a unique record

The MATCH function returns the relative position of an item in an array that matches a specified value

MATCH(0, COUNTIFS($F$2:$F2, $A$3:$A$26, $G$2:$G2, $B$3:$B$26, $H$2:$H2, $C$3:$C$26, $I$2:$I2, $D$3:$D$26), 0)

becomes

MATCH(0, {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, 0)

and returns 1.

Step 3 - Return a value of the cell at the intersection of a particular row and column

The INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX($A$3:$D$26, MATCH(0, COUNTIFS($F$2:$F2, $A$3:$A$26, $G$2:$G2, $B$3:$B$26, $H$2:$H2, $C$3:$C$26, $I$2:$I2, $D$3:$D$26), 0), COLUMN(A1))

becomes

INDEX($A$3:$D$26, 1, COLUMN(A1))

becomes

INDEX($A$3:$D$26, 1, 1)

becomes

INDEX({"Sample0", "B", 11, "AA111"; "Sample0", "B", 11, "AA110"; "Sample1", "A", 11, "AA111"; "Sample0", "A", 10, "AA111"; "Sample0", "B", 10, "AA110"; "Sample1", "B", 10, "AA111"; "Sample1", "B", 11, "AA111"; "Sample0", "A", 11, "AA110"; "Sample1", "A", 10, "AA110"; "Sample1", "A", 11, "AA110"; "Sample1", "A", 11, "AA111"; "Sample1", "A", 10, "AA110"; "Sample1", "B", 11, "AA110"; "Sample1", "A", 10, "AA111"; "Sample1", "B", 11, "AA111"; "Sample1", "B", 11, "AA111"; "Sample1", "A", 10, "AA110"; "Sample0", "A", 11, "AA110"; "Sample1", "B", 11, "AA110"; "Sample0", "B", 10, "AA111"; "Sample1", "A", 10, "AA110"; "Sample0", "A", 10, "AA110"; "Sample0", "A", 10, "AA110"; "Sample1", "A", 11, "AA110"}, 1, 1)

and returns "Sample0" in cell F3.

Back to top

Related articles

Recommended articles

Back to top

2. Filter unique distinct row records but not blanks

The image below shows you a data table in column A:D. Unfortunately, it has some blank rows, however, the formula below takes handles this issue.

Update 11 December 2020, Excel 365 formula in cell F3:

This is a much smaller formula also entered as a regular formula, you can read more about the UNIQUE function here: Extract unique distinct rows ignoring blank rows

The following array formula in cell F3 works with previous Excel versions:

Back to top

3. Extract unique distinct rows that don't begin with a given string

This example lets you specify a string and a record is shown in cell range A30:D36 if it does NOT match the beginning characters in column A and it is NOT a DUPLICATE record.

Update 11 December 2020, Excel 365 formula in cell A30:

The formula below is for previous Excel versions.

7. Extract unique distinct records based on condition, example 2

In this section, 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.

Excel 365 dynamic array formula in cell G6:

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

Back to top

8. 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 range B3:C6 and E3:F6.

If a record exists in both tables only one record is returned by the formula. If a record exists multiple times in one table only one record is returned by the formula.

Example, John 42 exists in both tables, however, the formula returns only one instance of John 42.

Laura 26 exists multiple times but only in the first table, the formula returns only one record of Laura 26.

Excel 365 dynamic array formula in cell B9:

Array formula in cell B9 for older Excel versions:

To enter an array formula, type the formula in cell B9 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.

What is a unique distinct record?

Unique distinct records are all records except duplicates merged into one distinct value.

In other words, duplicate records are removed.

Explaining the formula in cell B9

Use the "Evaluate Formula" tool to examine the calculation steps in greater detail.

Go to tab "Formula" on the ribbon, press with left mouse button on "Evaluate Formula" button to start the tool.

Then press with left mouse button on the "Evaluate" button to see the next step in the calculation, this will make it easier to understand how the formula works.

Step 1 - Count previous records against table 1

The COUNTIFS function allows you to count how many times a record exists in a table.

The previous values in cell B9 are the values in B8 and C8.

The first argument $B$8:B8 in the COUNTIFS function has both absolute and relative cell references, this allows the formula to automatically expand when you copy it and paste to cells below.

"Name" and "Age" is not found in cell range B3:C6 so the COUNTIFS function returns 0 (zero) for each record.

COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6)

becomes

=COUNTIFS("Name", {"John";"Laura";"Martin";"Laura"}, "Age", {42;26;45;26})

and returns {0;0;0;0}. There are four records and the function returns an array containing 4 values (zeros).

Step 2 - Find the first instance of 0 (zero) in the array

The MATCH function allows you to identify which record to return next.

MATCH(0, COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6), 0)

becomes

MATCH(0, {0;0;0;0}, 0)

and returns 1. The first instance of 0 (zero) is found in position 1 in the array.

Step 3 - Return value from a record

The INDEX function lets get a specific value using a row and column number.

The MATCH function calculates the row number we need to get the correct value, however, the COLUMNS function keeps track of which value in the record to get.

The COLUMNS function calculates the number of columns in a cell reference, the cell reference used here $A$1:A1 is also expanding when the formula is copied to other cells.

COLUMNS($A$1:A1) returns 1.

INDEX($B$3:$C$6, MATCH(0, COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6), 0),COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$6, 1, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$6, 1, 1) and returns John in cell B9.

Step 4 - IFNA function points the calculation in a new direction

Step 1 to 3 explains how the formula extracts unique distinct records from the first table.

The first part of the formula returns a #N/A error when there are no records left in table 1 to extract.

The IFNA function points the calculation to part2 when the error occurs.

The second part of the formula does the exact same thing as the first part except that the cell references this time points to the second table.

The picture above shows that the two last records are extracted from the second table.

Get Excel *.xlsx file

Extract unique distinct records from two data sets.xlsx

Back to top

9. Extract a unique distinct list and sum amounts based on a condition - earlier Excel versions

Anura asks:

Answer:

This post demonstrates how to build a formula to extract a unique distinct list using a condition, however, I highly recommend a pivot table for this task: Pivot table - Unique distinct list

Type a branch in cell G2 and the formulas in cell range F5:H7 returns the correct records from B3:D12. If you have more than two values in one record (except the number) use the COUNTIFS function to filter unique distinct records.

Array formula in F5:

Copy cell F5 and paste to cell range F5:G7.

Formula in cell H5:

If you have a version earlier than Excel 2007 use the SUMPRODUCT function

If you are interested in how the SUMPRODUCT function works, read:

Recommended articles

Watch a video where I explain the formula

Recommended link

Recommended articles

How to enter an array formula

1. Copy (Ctrl + c) above formula
2. Double press with left mouse button on cell E8
3. Paste (Ctrl + v) to cell
4. Press and hold CTRL + Shift simultaneously
5. Press Enter
6. Release all keys

Copy cell E8 and paste it down as far as needed. Copy cells and paste into cell range F8 and down as far as needed.

Recommended link

Recommended articles

Explaining array formula in cell E8

Step 1 - Check previous values to make sure they are not repeated

COUNTIF($E$7:$E7, $A$2:$A$11)

If you examine the cell references you may find this strange: $E$7:$E7 It is an absolute and relative cell reference. It expands when the formula is copied across the spreadsheet. Read more here: Absolute and relative references in excel

COUNTIF($E$7:$E7, $A$2:$A$11)

returns {0;0;0;0;0;0;0;0;0;0}

Step 2 - Compare values in column B with the value in cell F1

($B$2:$B$11<>$F$1)

returns {FALSE; ... ; FALSE}

Step 3 - Add arrays

COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1)

becomes

{0;0;0;0;0;0;0;0;0;0} + {FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}

and returns

{0;0;0;0;1;1;0;0;1;0}

Step 4 - Find first 0 (zero) in array

MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0)

becomes

MATCH(0, {0;0;0;0;1;1;0;0;1;0}, 0)

and returns 1.

Step 5 - Return a value or reference of the cell at the intersection of a particular row and column

INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1))

returns "Frank" in cell E8.

Get Excel *.xls file

unique distinct list matching criteria.xls
(Excel 97-2003 Workbook *.xls)

10. Extract a unique distinct list and sum amounts based on a condition - Excel 365

The following dynamic Excel 365 formula aggregates or groups the amounts based "Name" and the specified "Branch" in cell C14, and then sorts the output array based on the amounts.

The image above shows this data table in cell range B2:D12:

Name	Branch	Amount
Frank	Branch A	10
Frank	Branch A	20
Frank	Branch A	40
Joe	Branch A	30
Mary	Branch B	5
Jane	Branch C	10
Mike	Branch A	15
Joe	Branch A	40
Dave	Branch C	35
Joe	Branch A	20

Formula in cell B17:

The formula in cell B17 returns this dynamic array:

Joe	Branch A	90
Frank	Branch A	70
Mike	Branch A	15

It contains only data based on the specified "Branch" in cell C14 which is "Branch A" in this example. "Branch A" has the following names: Joe, Frank, and Mike and the corresponding amounts are 90, 70, and 15.

Here's a breakdown of the formula:

1. SUMIFS(D3:D12,C3:C12,C3:C12,B3:B12,B3:B12): This function calculates the sum of values in column D for each row where the values in columns C and B match the values in the same row. It's essentially a self-referential sumifs that sums up the values in column D for each unique combination of values in columns C and B.
2. HSTACK(B3:C12,SUMIFS(...)): This function horizontally stacks (or combines) the values in columns B and C with the results of the sumifs function. This creates a new array with the values from columns B and C, and the corresponding sumifs results.
3. FILTER(HSTACK(...),C3:C12=C14): This function filters the array created by the hstack function to only include rows where the value in column C matches the value in cell C14.
4. UNIQUE(FILTER(...)): This function removes any duplicate rows from the filtered array, leaving only unique combinations of values.
5. SORT(UNIQUE(...),3,-1): This function sorts the unique array in descending order (due to the -1) based on the values in the third column which is the sumifs results.

10.1 Explaining formula

Step 1 - Filter data based on given branch

The SUMIFS function adds numbers based on criteria.

Function syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

SUMIFS(D3:D12,C3:C12,C3:C12,B3:B12,B3:B12)

returns

Step 2 - Stack arrays horizontally

The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Function syntax: HSTACK(array1,[array2],...)

HSTACK(B3:C12,SUMIFS(D3:D12,C3:C12,C3:C12,B3:B12,B3:B12))

returns

Step 3 - Filter data based on given branch

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(HSTACK(B3:C12,SUMIFS(D3:D12,C3:C12,C3:C12,B3:B12,B3:B12)),C3:C12=C14)

returns

Step 4 - List unique distinct rows

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(HSTACK(B3:C12,SUMIFS(D3:D12,C3:C12,C3:C12,B3:B12,B3:B12)),C3:C12=C14))

returns

Step 5 - Sort array in descending order based on amounts in column 3

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT(UNIQUE(FILTER(HSTACK(B3:C12,SUMIFS(D3:D12,C3:C12,C3:C12,B3:B12,B3:B12)),C3:C12=C14)),3,-1)

returns

Unique distinct records category

More than 1300 Excel formulas

Excel categories

Comments (5)