2 questions

M

Mark1

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!
 
M

Max

Mark1 said:
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.
Click to expand...

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
 
G

Gord Dibben

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
 
Top