I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

I would suggest creating a list of the names that are in the Accounting
& Land Departments, then create an extra column and use a match formula
to see if their name is in the lookup table, if so assign a value of 1
else 0. then you could use autoFilter to filter the 1's
Example: Names are in Col A Sheet 1, Lookup Table is in Col A Sheet 2
with a Named Range of "Acc_Land"
=IF(NOT(ISERROR(MATCH(A2,Acc_Land,2))),1,0)

Charles Chickering

Dtown Dawg wrote:
I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

Thank you.
After doing some research on this, the consensus seems that it is
recommended to use that Autofilter rather than a macro or VBA...Is this
because the Autofilter is faster? Just curious.
Thanks;
Dustin

"Tom Ogilvy" wrote:

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

Another Thing.
The department that the employee is in, is not listed. THe only thing listed
is
Colum A: IP Address
Column B: Username
Column C: Date
Column D: Time

Everytime the user logs in a entry is made, and everytime the user logs out
a entry is made (Time In, Time out).

So I think I just need to do that DATA|Filter|Autofilter and then just start
selecting name by name.

"Tom Ogilvy" wrote:

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

Dawg, if you can generate a list of names that are in the Accounting
department, then you can use AutoFilter with a macro which would
automate everything for you.

Charles

Dtown Dawg wrote:
Another Thing.
The department that the employee is in, is not listed. THe only thing listed
is
Colum A: IP Address
Column B: Username
Column C: Date
Column D: Time

Everytime the user logs in a entry is made, and everytime the user logs out
a entry is made (Time In, Time out).

So I think I just need to do that DATA|Filter|Autofilter and then just start
selecting name by name.

"Tom Ogilvy" wrote:

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

If you want a macro, turn on the macro recorder while you do it one time
manually.

then generalize the macro. then you can run the macro and *it* does the
autofilter.

So no, there wouldn't be any speed difference if the macro was written this
way. If you were using

ActiveCell.offset(-1,0).Select

kind of code, then yes, the autofilter would more than likely be faster.

It is best to utilize the best capabilities excel provides in most cases,
even it it is to use them in a macro.

--
Regards,
Tom Ogilvy



"Dtown Dawg" wrote:

Thank you.
After doing some research on this, the consensus seems that it is
recommended to use that Autofilter rather than a macro or VBA...Is this
because the Autofilter is faster? Just curious.
Thanks;
Dustin

"Tom Ogilvy" wrote:

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

No, that would be a non-starter <g

if you have a list of names you do want,

say go to J1
J1: Username
J2: paste your list of names so they go down column J

now select A1:D1 down to the last cell you want
Or go to the name box and type in A1:D37000

do data=filter=Advanced Filter
your data should be shown as the database
Click in criteria and then select J1 down to the last name,

then select copy to another location and click on destination, then click on
a cell such as L1.

then click OK.



--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

Another Thing.
The department that the employee is in, is not listed. THe only thing listed
is
Colum A: IP Address
Column B: Username
Column C: Date
Column D: Time

Everytime the user logs in a entry is made, and everytime the user logs out
a entry is made (Time In, Time out).

So I think I just need to do that DATA|Filter|Autofilter and then just start
selecting name by name.

"Tom Ogilvy" wrote:

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?

I'm trying this, but I can't get it to work, the only thing that pastes over
in L1 is the data that is in the 1st row A1:D1 That is all that shows up.
Any suggestions.

"Tom Ogilvy" wrote:

No, that would be a non-starter <g

if you have a list of names you do want,

say go to J1
J1: Username
J2: paste your list of names so they go down column J

now select A1:D1 down to the last cell you want
Or go to the name box and type in A1:D37000

do data=filter=Advanced Filter
your data should be shown as the database
Click in criteria and then select J1 down to the last name,

then select copy to another location and click on destination, then click on
a cell such as L1.

then click OK.



--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

Another Thing.
The department that the employee is in, is not listed. THe only thing listed
is
Colum A: IP Address
Column B: Username
Column C: Date
Column D: Time

Everytime the user logs in a entry is made, and everytime the user logs out
a entry is made (Time In, Time out).

So I think I just need to do that DATA|Filter|Autofilter and then just start
selecting name by name.

"Tom Ogilvy" wrote:

select the data, do Data=filter=Autofilter

in the dropdown in the departments column, select
custom and then does not equal Accounting & Land or whatever the title is

then all the records you want to delete should be visible

in the name box enter

2:37000 and hit enter
then do Edit=Delete
now do Data=filter=Autofilter to remove the autofilter and the Accounting
& Land records should remain.

Obviously do this on a copy of your data.

--
Regards,
Tom Ogilvy


"Dtown Dawg" wrote:

I have an xls spreadsheet that contains a list of user names and when they
log onto the VPN and when they log off the VPN.

This xls spreadsheets contains 37,000 rows of information. However this
includes all deparments, and I only need to do a report on the Accounting &
Land Department.

Currently I go through the xls sheet and delete all the usernames that do
not fall into the Accounting & Land department. This is tedious and time
consuming.

I would like to be able to enter all the usernames that I need to delete
into a macro or VB. Then that way I could run the macro, all the usernames I
don't need would be deleted and then I could start gathering information for
my reports each month.

Can somebody please help or point me in the correct direction, that is if
this is possible?