Article updated on March 12, 2009

In this article I will try to automatically filter unique row records from multiple columns, without using arrays. To do that, I need to use "helper" columns.

Here are previous articles on this subject:
Automatically filter unique row records from multiple columns
Lookup Unique based on Multiple Conditions
How to count unique combined column values

Here is the table that I will extract unique records from:

First I concatenate A2:D25 with this formula in cells A28:A51
=A2&B2&C2&D2 in cell A28 and copied down to row A51.

In next column i use =MATCH(A28;$A$28:$A$51;0) in cells B28:A51

In cell C28 to C51: =IF(B28>=ROW()-27;INDEX($A$2:$D$25;ROW()-27;1);"")
D28 to D51: =IF(B28>=ROW()-27;INDEX($A$2:$D$25;ROW()-27;2);"")
E28 to E51: =IF(B28>=ROW()-27;INDEX($A$2:$D$25;ROW()-27;3);"")
F28 to F51: =IF(B28>=ROW()-27;INDEX($A$2:$D$25;ROW()-27;4);"")

The IF() function in the above formulas, checks if the current row number is smaller or equal to column B. That will create a list of unique cell content. The downside is that the list will have blanks where a duplicate is.
See this article on how to remove blank cells: Remove blank cells

Here is a picture of the helper columns and the unique list:


Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)

Excel functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

ROW(reference) returns the rownumber of a reference

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