Question: How to extract unique records spanning over multiple columns from a list?

Answer:

Here is the complicated formula that i will try to explain.

=IF(ISERROR(INDEX($A$2:$D$25,IF(LARGE(MATCH( A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)* ((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25 ,0))>=(ROW(A2:A25)-(MIN(ROW(A2:A25))-1))),ROW(INDIRECT("1:"&ROW()-1)))=0,"", LARGE(MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)* ((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0))>= (ROW(A2:A25)-(MIN(ROW(A2:A25))-1))),ROW(INDIRECT("1:"&ROW()-1)))), COLUMN()-6)),"",INDEX($A$2:$D$25,IF(LARGE( MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)* ((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0))>= (ROW(A2:A25)-(MIN(ROW(A2:A25))-1))),ROW(INDIRECT("1:"&ROW()-1)))=0,"", LARGE(MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)* ((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0))>= (ROW(A2:A25)-(MIN(ROW(A2:A25))-1))),ROW(INDIRECT("1:"&ROW()-1)))),COLUMN()-6))

Here is the most important part. This part of the formula filters out the unique values and returns the row numbers of the unique values.

LARGE(MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)* ((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0))>= (ROW(A2:A25)-(MIN(ROW(A2:A25))-1))),ROW(INDIRECT("1:"&ROW()-1)))

Let us see in detail what this does.

MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0) gives us the the first row number of each row record. There can be multiple records that are the same, but only the row number of the first match. In this case: {1,2,3,4,2,2,7,3,9,9,9,12,13,3,15,15,17,12,1,20,21,3,4,21}. As you can see, the fith and the sixth array record returns row number 2. That means they are identical.

((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25 ,0))>=(ROW(A2:A25)-(MIN(ROW(A2:A25))-1))). This part of the formula checks if the array is bigger or equal to the adjacent row numbers.

{1,2,3,4,2,2,7,3,9,9,9,12,13,3,15,15,17,12,1,20,21,3,4,21} >= {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24}.

This will return

{TRUE,TRUE,TRUE, TRUE, FALSE, FALSE,TRUE, FALSE,TRUE, FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE, FALSE} Now why is this part of the formula essential to understand? It gives us the row numbers that are have unique values. As you can see here:

MATCH( A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25,0)* ((MATCH(A2:A25&B2:B25&C2:C25&D2:D25,A2:A25&B2:B25&C2:C25&D2:D25 ,0))>=(ROW(A2:A25)-(MIN(ROW(A2:A25))-1)))

{1,2,3,4,2,2,7,3,9,9,9,12,13,3,15,15,17,12,1,20,21,3,4,21} * {TRUE,TRUE,TRUE, TRUE, FALSE, FALSE,TRUE, FALSE,TRUE, FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE, FALSE} = {1,2,3,4,0,0,7,0,9,0,0,12,13,0,15,0,17,0,0,20,21,22,0,0,0}

With large() function we can now extract the essential row numbers and with index() the cell values corresponding to the rownumbers.

The rest of the formula is error management and repetition of the previous formula.

Download excel sample file for this tutorial.
automatically-filter-unique-row-records-from-multiple-columns
(Excel 97-2003 Workbook *.xls)

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

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

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

Related posts:

1. A more userfriendly version of: Automatically filter unique row records from multiple columns
2. Unique distinct values from multiple columns using array formula
3. Find missing numbers in a range from multiple columns
4. Identify duplicate invoice records in excel
5. Create unique list from two columns
6. Filter common values from three columns in excel
7. Filter unique distinct values from two ranges combined in excel 2007