## Filter unique distinct records

**Table of contents**

- Filter unique distinct row records
- Filter unique distinct row records but not blanks
- Filter unique distinct row records that does not begin with *string*
- Filter unique distinct row records that begins with *string*
- Filter unique distinct records [Pivot table]

**Question: **How do I extract unique distinct records from this list?

**Answer:**

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 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.

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

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

Cell range A3:D26 contains the original list, cell range F3:I16 contains the filtered list.

**Array formula in cell F3:**

#### How to enter an array formula

- Select cell F3
- Type above formula in cell or formula bar
- Press and hold CTRL + SHIFT simultaneously
- 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.

#### 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.

#### 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 click "Evaluate Formula" button. Click "Evaluate" button to move to next step.

**Step 1 - Identify rows with unique records**

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))

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**

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.

### Download excel *.xlsx file

The workbook contains all five examples on five different worksheets.

Filter unique distinct records.xlsx

(Excel 2007 Workbook *.xlsx)

#### Related 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 […]

5 easy ways to extract Unique Distinct Values

### How to remove blank rows

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

**Array formula in cell F3:**

### Filter unique distinct row records that does not begin with *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.

**Array formula in cell A30:**

### Filter unique distinct row records that begins 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.

**Array formula in cell A30:**

### Download excel *.xlsx file

Filter unique distinct records.xlsx

(Excel 2007 Workbook *.xlsx)

#### Related articles

Extract unique distinct values if value contains string

The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]

Extract unique distinct values if value contains string

### Functions used in this formula:

**MATCH(**lookup_value,lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

**SEARCH**(*find_text*,*within_text*, [*start_num*])

Returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

Lookup multiple values across columns and return a single value

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Prevent duplicate records in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

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.

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 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 […]

### 2 Responses to “Filter unique distinct records”

### 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.

**Contact Oscar**

You can contact me through this contact form

[...] Filed in Excel on Feb.02, 2009. Email This article to a Friend 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 [...]

[...] We can verify the calculation by extracting all unqiue distinct records from the above table. I am using a formula from this blog article: Filter unique distinct row records in excel 2007 [...]