Filter unique distinct row records in excel 2007
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:
Download excel sample file for this tutorial.
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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
March 14th, 2009 at 10:43 am
[...] 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 [...]
January 17th, 2011 at 10:14 am
[...] 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 [...]