## Filter unique distinct row 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*

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

**Answer:**

A record is an entire row, example A2:D2 = Sample1, B, 10, AA110. See picture above.

Unique distinct records are all records but duplicate records are removed.

Cell range A2:D25 contains the original list.

Cell range A29:D43 contains the filtered list.

### Excel 2007 array formula in cell A29:

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

### Explaining array formula in cell A29

=INDEX($A$2:$D$25, MATCH(0, COUNTIFS($A$28:$A28, $A$2:$A$25, $B$28:$B28, $B$2:$B$25, $C$28:$C28, $C$2:$C$25, $D$28:$D28, $D$2:$D$25), 0), COLUMN(A1))

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

=INDEX($A$2:$D$25, MATCH(0, COUNTIFS($A$28:$A28, $A$2:$A$25, $B$28:$B28, $B$2:$B$25, $C$28:$C28, $C$2:$C$25, $D$28:$D28, $D$2:$D$25), 0), COLUMN(A1))

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...) counts the number of cells specified by a given set of conditions or criteria

COUNTIFS($A$28:$A28, $A$2:$A$25, $B$28:$B28, $B$2:$B$25, $C$28:$C28, $C$2:$C$25, $D$28:$D28, $D$2:$D$25)

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

=INDEX($A$2:$D$25, **MATCH(0, COUNTIFS($A$28:$A28, $A$2:$A$25, $B$28:$B28, $B$2:$B$25, $C$28:$C28, $C$2:$C$25, $D$28:$D28, $D$2:$D$25), 0)**, COLUMN(A1))

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH(0, COUNTIFS($A$28:$A28, $A$2:$A$25, $B$28:$B28, $B$2:$B$25, $C$28:$C28, $C$2:$C$25, $D$28:$D28, $D$2:$D$25), 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(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

=INDEX($A$2:$D$25, MATCH(0, COUNTIFS($A$28:$A28, $A$2:$A$25, $B$28:$B28, $B$2:$B$25, $C$28:$C28, $C$2:$C$25, $D$28:$D28, $D$2:$D$25), 0), COLUMN(A1))

becomes

=INDEX($A$2:$D$25, 1, COLUMN(A1))

becomes

=INDEX($A$2:$D$25, 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 A29.

### How to remove blank rows

Array formula in cell A29:

### Filter unique distinct row records that does not begin with *string*

**Array formula in cell E3:**

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

### Download excel *.xlsx file

Filter unique distinct records.xlsx

(Excel 2007 Workbook *.xlsx)

### Recommended blog post

Filter duplicate rows in excel 2007

Highlight duplicate rows in excel 2007

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

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