Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro to Delete All Rows that meet a certain criteria
I have a security log which shows each date/time a user is admitted and then
enters through a secure door in our office suite using their security badge. I need a macro that will remove all the rows in the spreadsheet exept the earliest access time and the latest access time for each date. Ultimately I need to determine how long a user was in the office suite each day, assuming that the last card swipe was made when the user left the office at the end of the day. Here is some example data: Admitted 10/17/2007 12:23 Entered 10/17/2007 12:23 Admitted 10/17/2007 14:37 Entered 10/17/2007 14:37 Admitted 10/17/2007 16:55 Entered 10/17/2007 16:55 Admitted 10/17/2007 18:31 Admitted 10/18/2007 10:13 Entered 10/18/2007 10:13 Admitted 10/18/2007 14:11 Entered 10/18/2007 14:11 Admitted 10/18/2007 17:39 Entered 10/18/2007 17:39 Admitted 10/18/2007 17:43 Entered 10/18/2007 17:43 Admitted 10/19/2007 10:46 Entered 10/19/2007 10:46 Admitted 10/19/2007 16:39 Entered 10/19/2007 16:39 Admitted 10/19/2007 18:40 Entered 10/19/2007 18:40 Admitted 10/19/2007 21:13 Admitted 10/22/2007 10:33 Entered 10/22/2007 10:33 Admitted 10/22/2007 13:17 Entered 10/22/2007 13:17 Admitted 10/22/2007 14:03 Entered 10/22/2007 14:03 Admitted 10/22/2007 15:58 Entered 10/22/2007 15:58 Admitted 10/22/2007 18:51 Desired Result: Admitted 10/17/2007 12:23 Admitted 10/17/2007 18:31 Admitted 10/18/2007 10:13 Entered 10/18/2007 17:43 (users do not always swipe card before leaving) Admitted 10/19/2007 10:46 Admitted 10/19/2007 21:13 Admitted 10/22/2007 10:33 Admitted 10/22/2007 18:51 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro to Delete All Rows that meet a certain criteria
A quick, non-macro method to solve this would be to insert a row above row
1 and enter column labels to make your data look as follows: Action Date Time Delete Admitted 10/17/2007 12:23 Entered 10/17/2007 12:23 Admitted 10/17/2007 14:37 Entered 10/17/2007 14:37 Admitted 10/17/2007 16:55 Entered 10/17/2007 16:55 Admitted 10/17/2007 18:31 Admitted 10/18/2007 10:13 Entered 10/18/2007 10:13 Admitted 10/18/2007 14:11 Entered 10/18/2007 14:11 Admitted 10/18/2007 17:39 Entered 10/18/2007 17:39 Admitted 10/18/2007 17:43 Entered 10/18/2007 17:43 Admitted 10/19/2007 10:46 Entered 10/19/2007 10:46 Admitted 10/19/2007 16:39 Entered 10/19/2007 16:39 Admitted 10/19/2007 18:40 Entered 10/19/2007 18:40 Admitted 10/19/2007 21:13 Admitted 10/22/2007 10:33 Entered 10/22/2007 10:33 Admitted 10/22/2007 13:17 Entered 10/22/2007 13:17 Admitted 10/22/2007 14:03 Entered 10/22/2007 14:03 Admitted 10/22/2007 15:58 Entered 10/22/2007 15:58 Admitted 10/22/2007 18:51 Then, in cell $D$2, enter the formula: =IF(AND(B2=B1,B2=B3),"Delete","") ....and fill down. Then use AutoFilter to show only rows that are blank in column $D. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula/Macro to delete rows that do not meet criteria from a list? | Excel Worksheet Functions | |||
Delete rows that do not meet specific criteria | Excel Programming | |||
Macro, delete rows that meet criteria | Excel Programming | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
how do i delete rows when cells meet certain criteria? | Excel Programming |