"Mark1" wrote in message
...
Is there a function or formula that will automatically filter repeats out
of
a list? If I have account numbers in column A, and some of them are
repeated, I want the account numbers in column B without repeats. I can
use
advanced filter but I wanted a functional way that will automatically
update
when I update column A.

For the above Q ..

One way ..

Assume acc nos are in col A, in A2 down

Put in C2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW()))
(ensure C1 is left blank)

Put in B2:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select B2:C2, fill down to say C1000,
to cover the max expected data range in col A

Col B will return a unique list of acc nos from those in col A

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----