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

Get the Excel file

Filter-unique-distinct-records.xlsx

Related articles

Recommended articles

5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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.

Array formula in cell A30:

=INDEX($A$2:$D$26, MATCH(0, COUNTIFS($A$29:$A29, $A$2:$A$26, $B$29:$B29, $B$2:$B$26, $C$29:$C29, $C$2:$C$26, $D$29:$D29, $D$2:$D$26)+IFERROR(SEARCH($G$29, $A$2:$A$26)=1, 0)+0, 0), COLUMN(A1))

How to enter an array formula

Back to top

4. Extract unique distinct rows that begin with *string*

If you are looking for records that begin with *string*, see this formula. Note that the formula looks for values in col A that begins with a specific text string.

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

=UNIQUE(FILTER(A30:D36, A30:A36=G29))

The formula below is for previous Excel versions.

Array formula in cell A30:

=INDEX($A$2:$D$26, MATCH(0, COUNTIFS($A$29:$A29, $A$2:$A$26, $B$29:$B29, $B$2:$B$26, $C$29:$C29, $C$2:$C$26)+IFERROR(NOT(SEARCH($G$29, $A$2:$A$26)=1), 1)+0, 0), COLUMN(A1))

Back to top

Get the Excel file

Filter-unique-distinct-recordsv2.xlsx

Back to top

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

Comments (2)