Table of contents

  1. Filter unique distinct row records
  2. Filter unique distinct row records but not blanks
  3. Filter unique distinct row records that does not begin with *string*
  4. 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:

=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)) + CTRL + SHIFT + ENTER

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:

=INDEX($A$2:$D$25, MATCH(0, IF(($A$2:$A$25<>"")+($B$2:$B$25<>"")+($C$2:$C$25<>"")+($D$2:$D$25<>""), 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), 1), 0), COLUMN(A1))

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

filter-unique-distinct-records-but-exclude-string-begins-with

Array formula in cell E3:

=INDEX($A$2:$C$11, MATCH(0, COUNTIFS($E$2:$E2, $A$2:$A$11, $F$2:$F2, $B$2:$B$11, $G$2:$G2, $C$2:$C$11)+IFERROR(SEARCH($F$1, $A$2:$A$11)=1, 0)+0, 0), COLUMN(A1))

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.

=INDEX($A$2:$C$11, MATCH(0, COUNTIFS($E$2:$E2, $A$2:$A$11, $F$2:$F2, $B$2:$B$11, $G$2:$G2, $C$2:$C$11)+IFERROR(NOT(SEARCH($F$1, $A$2:$A$11)=1), 1)+0, 0), COLUMN(A1))

filter-unique-distinct-records-and-include-string-begins-with

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)