Automatic unique records filter macro

I continuously update a database - it has multiple columns - and have
assigned a dynamic range name to it ("List"). Since the database has
some duplicate records, very often I end up performing the Data/
Filter/Advanced Filter routine and copying the unique records onto
another sheet (same workbook).

Would it be possible to automate this command? Is there a macro that
would copy the unique records to another sheet automatically, while
the workbook is open, without having to push a command button, or
opening/closing the file? Thanks in advance.

Regards,
George Ferrer

I would create my own macro.

I'd start by recording a macro when I did all the work manually.

gferrer010@gmail.com wrote:
>
> I continuously update a database - it has multiple columns - and have
> assigned a dynamic range name to it ("List"). Since the database has
> some duplicate records, very often I end up performing the Data/
> Filter/Advanced Filter routine and copying the unique records onto
> another sheet (same workbook).
>
> Would it be possible to automate this command? Is there a macro that
> would copy the unique records to another sheet automatically, while
> the workbook is open, without having to push a command button, or
> opening/closing the file? Thanks in advance.
>
> Regards,
> George Ferrer

--

Dave Peterson

On Jul 9, 4:57*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I would create my own macro.
>
> I'd start by recording a macro when I did all the work manually.
>
> gferrer...@gmail.com wrote:
>
> > I continuously update a database - it has multiple columns - and have
> > assigned a dynamic range name to it ("List"). Since the database has
> > some duplicate records, very often I end up performing the * Data/
> > Filter/Advanced Filter routine and copying the unique records onto
> > another sheet (same workbook).
>
> > Would it be possible to automate this command? Is there a macro that
> > would copy the unique records to another sheet automatically, while
> > the workbook is open, without having to push a command button, or
> > opening/closing the file? Thanks in advance.
>
> > Regards,
> > George Ferrer
>
> --
>
> Dave Peterson

Dave,

If I record a a macro I still have to use the command/radio buttons in
order to execute the macro though. If possible, I'd rather avoid
that. The reason being that another person is going to take over the
maintenance of this database and he doesn't even know the basics of
Excel.
I'm not even sure if the automation of this task is possible. Is there
a VBA code that would do this?

Thanks,
George

The recorded code won't show the dialogs for you to chose options. It'll just
use the options that you used when you recorded the macro.

Try recording the macro and see if it gets close to what you want. If you have
trouble tweaking it, post back with your macro and what tweaks you need.

gferrer010@gmail.com wrote:
>
> On Jul 9, 4:57 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > I would create my own macro.
> >
> > I'd start by recording a macro when I did all the work manually.
> >
> > gferrer...@gmail.com wrote:
> >
> > > I continuously update a database - it has multiple columns - and have
> > > assigned a dynamic range name to it ("List"). Since the database has
> > > some duplicate records, very often I end up performing the Data/
> > > Filter/Advanced Filter routine and copying the unique records onto
> > > another sheet (same workbook).
> >
> > > Would it be possible to automate this command? Is there a macro that
> > > would copy the unique records to another sheet automatically, while
> > > the workbook is open, without having to push a command button, or
> > > opening/closing the file? Thanks in advance.
> >
> > > Regards,
> > > George Ferrer
> >
> > --
> >
> > Dave Peterson
>
> Dave,
>
> If I record a a macro I still have to use the command/radio buttons in
> order to execute the macro though. If possible, I'd rather avoid
> that. The reason being that another person is going to take over the
> maintenance of this database and he doesn't even know the basics of
> Excel.
> I'm not even sure if the automation of this task is possible. Is there
> a VBA code that would do this?
>
> Thanks,
> George

--

Dave Peterson

On Jul 9, 12:20*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> The recorded code won't show the dialogs for you to chose options. *It'll just
> use the options that you used when you recorded the macro.
>
> Try recording the macro and see if it gets close to what you want. *If you have
> trouble tweaking it, post back with your macro and what tweaks you need.
>
>
>
>
>
> gferrer...@gmail.com wrote:
>
> > On Jul 9, 4:57 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > I would create my own macro.
>
> > > I'd start by recording a macro when I did all the work manually.
>
> > > gferrer...@gmail.com wrote:
>
> > > > I continuously update a database - it has multiple columns - and have
> > > > assigned a dynamic range name to it ("List"). Since the database has
> > > > some duplicate records, very often I end up performing the * Data/
> > > > Filter/Advanced Filter routine and copying the unique records onto
> > > > another sheet (same workbook).
>
> > > > Would it be possible to automate this command? Is there a macro that
> > > > would copy the unique records to another sheet automatically, while
> > > > the workbook is open, without having to push a command button, or
> > > > opening/closing the file? Thanks in advance.
>
> > > > Regards,
> > > > George Ferrer
>
> > > --
>
> > > Dave Peterson
>
> > Dave,
>
> > If I record a a macro I still have to use the command/radio buttons in
> > order to execute the macro though. If possible, I'd *rather avoid
> > that. The reason being that another person is going to take over the
> > maintenance of this database and he doesn't even know the basics of
> > Excel.
> > I'm not even sure if the automation of this task is possible. Is there
> > a VBA code that would do this?
>
> > Thanks,
> > George
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -



Dave, here is the macro that I came up with:

Sub ExtractUniqueRecords()

Keyboard Shortcut: Ctrl+z

Range("List”).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"A2"), Unique:=True

End Sub

This seems to work OK, but I either have to use the keyboard shortcut
or assign a button in order to run it. It's not capable of running by
itself, without any (manual) commands.

That seems pretty reasonable to me.

Why and when would you want it to run automatically?



gferrer010@gmail.com wrote:
>
<<snipped>>
>
> Dave, here is the macro that I came up with:
>
> Sub ExtractUniqueRecords()
>
> Keyboard Shortcut: Ctrl+z
>
> Range("List”).AdvancedFilter Action:=xlFilterCopy,
> CopyToRange:=Range( _
> "A2"), Unique:=True
>
> End Sub
>
> This seems to work OK, but I either have to use the keyboard shortcut
> or assign a button in order to run it. It's not capable of running by
> itself, without any (manual) commands.

--

Dave Peterson

On Jul 9, 2:29*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> That seems pretty reasonable to me. *
>
> Why and when would you want it to run automatically?
>
>
>
>
>
>
>
> gferrer...@gmail.com wrote:
>
> <<snipped>>
>
> > Dave, here is the macro that I came up with:
>
> > Sub ExtractUniqueRecords()
>
> > *Keyboard Shortcut: Ctrl+z
>
> > *Range("List”).AdvancedFilter Action:=xlFilterCopy,
> > CopyToRange:=Range( _
> > * * "A2"), Unique:=True
>
> > End Sub
>
> > This seems to work OK, but I either have to use the keyboard shortcut
> > or assign a button in order to run it. It's not capable of running by
> > itself, without any (manual) commands.
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -


I just want to eliminate any potential mistakes, since the person
who's gonna be doing the data entry is rather mistake prone, and is
most likely going to forget to use the macro command. Automating the
whole thing whould eliminate lots of duplication errors. Ideally the
macro would run by itself as soon as new pieces of data/records are
added to the database.

Personally, I wouldn't do it that way.

If your database has 18 fields, you'd choose one to indicate that you're done
with the data entry (the 18th field???). Then if the user notices a typo, your
automatic filter may have hidden the row and it may be difficult to find. Or if
the user just starts entering data in your indicator field, it could be a
problem for the user.

But if you wanted, you could use an event macro (worksheet_change) that would
look for changes in certain ranges and run your routine.

But I don't know enough to offer any real details.

gferrer010@gmail.com wrote:
>
> On Jul 9, 2:29 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > That seems pretty reasonable to me.
> >
> > Why and when would you want it to run automatically?
> >
> >
> >
> >
> >
> >
> >
> > gferrer...@gmail.com wrote:
> >
> > <<snipped>>
> >
> > > Dave, here is the macro that I came up with:
> >
> > > Sub ExtractUniqueRecords()
> >
> > > Keyboard Shortcut: Ctrl+z
> >
> > > Range("List”).AdvancedFilter Action:=xlFilterCopy,
> > > CopyToRange:=Range( _
> > > "A2"), Unique:=True
> >
> > > End Sub
> >
> > > This seems to work OK, but I either have to use the keyboard shortcut
> > > or assign a button in order to run it. It's not capable of running by
> > > itself, without any (manual) commands.
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
>
> I just want to eliminate any potential mistakes, since the person
> who's gonna be doing the data entry is rather mistake prone, and is
> most likely going to forget to use the macro command. Automating the
> whole thing whould eliminate lots of duplication errors. Ideally the
> macro would run by itself as soon as new pieces of data/records are
> added to the database.

--

Dave Peterson