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.

If I have three copies of a workbook on three different drives, is there a
way to make changes to one that will automatically update the other two
copies?

Thanks!

"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
----

Mark1

Second question........

You can use a macro.

Sub BUandSave2()
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="E:\GordStuff\" & _
ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs Filename:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

This code could be part of a Before_Close routine in ThisWorkbook also if you
don't want to click a button to run the code.


Gord Dibben Excel MVP

On Tue, 15 Mar 2005 08:49:02 -0800, "Mark1"
wrote:

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.

If I have three copies of a workbook on three different drives, is there a
way to make changes to one that will automatically update the other two
copies?

Thanks!