Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Delete Rows that fit a certain criteria
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Delete Rows that fit a certain criteria
We have a security log that tracks, on a daily basis, each time a user enters
our office suite using their security badge. What we are looking for is a
way to remove all the rows from this spreadsheet except for the rows which
contain the first and last time per day the user accessed a badge reader.

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

What we wouuld like to get in the end would be:

Admitted 10/17/2007 12:23
Admitted 10/17/2007 18:31
Entered 10/18/2007 10:13
Entered 10/18/2007 17:43
Admitted 10/19/2007 10:46
Admitted 10/19/2007 21:13
Entered 10/22/2007 10:33
Admitted 10/22/2007 18:51

Thanks very much!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,069
Default Delete Rows that fit a certain criteria
Assuming your example data is in columns A, B, and C
and has headings in row 1 (or a blank row 1):

1. Sort by Date and Time in ascending order
2. Enter the following formula into D2 & copy down
through all rows of data:
=IF(B2<B1,"First",IF(B2<B3,"Last",""))

3. Copy & paste column D in place as values
4. Sort by column D in descending order
5. Delete rows where column D is blank
6. Sort by Date & Time in ascending order

Hope this helps,

Hutch

"jpittari" wrote:

We have a security log that tracks, on a daily basis, each time a user enters
our office suite using their security badge. What we are looking for is a
way to remove all the rows from this spreadsheet except for the rows which
contain the first and last time per day the user accessed a badge reader.

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

What we wouuld like to get in the end would be:

Admitted 10/17/2007 12:23
Admitted 10/17/2007 18:31
Entered 10/18/2007 10:13
Entered 10/18/2007 17:43
Admitted 10/19/2007 10:46
Admitted 10/19/2007 21:13
Entered 10/22/2007 10:33
Admitted 10/22/2007 18:51

Thanks very much!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,069
Default Delete Rows that fit a certain criteria
Here is a macro which performs the steps outlined below. Again, it assumes
your example data is in columns A, B, and C, and that your data begins on row
2.

Sub Macro1()
Dim LastRow As Long
LastRow& = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").Select
Range("A1:C" & LastRow&).Sort Key1:=Range("B2"), _
Order1:=xlAscending, Key2:=Range("C2"), _
Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("D2").Select
Selection.FormulaR1C1 =
"=IF(RC[-2]<R[-1]C[-2],""First"",IF(RC[-2]<R[1]C[-2],""Last"",""""))"
Selection.AutoFill Destination:=Range("D2:D" & LastRow&)
Range("D2:D" & LastRow&).Select
Columns("D:D").Select
Columns("D:D").Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("D2").Select
Range("A1:D" & LastRow&).Sort Key1:=Range("D2"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D2").Activate
Do While Len(ActiveCell.Value) 0
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Select
Range(ActiveCell, "D" & LastRow&).Select
Selection.EntireRow.Delete
LastRow& = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:D" & LastRow&).Sort Key1:=Range("B2"), _
Order1:=xlAscending, Key2:=Range("C2"), _
Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

Hope this helps,

Hutch

"Tom Hutchins" wrote:

Assuming your example data is in columns A, B, and C
and has headings in row 1 (or a blank row 1):

1. Sort by Date and Time in ascending order
2. Enter the following formula into D2 & copy down
through all rows of data:
=IF(B2<B1,"First",IF(B2<B3,"Last",""))

3. Copy & paste column D in place as values
4. Sort by column D in descending order
5. Delete rows where column D is blank
6. Sort by Date & Time in ascending order

Hope this helps,

Hutch

"jpittari" wrote:

We have a security log that tracks, on a daily basis, each time a user enters
our office suite using their security badge. What we are looking for is a
way to remove all the rows from this spreadsheet except for the rows which
contain the first and last time per day the user accessed a badge reader.

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

What we wouuld like to get in the end would be:

Admitted 10/17/2007 12:23
Admitted 10/17/2007 18:31
Entered 10/18/2007 10:13
Entered 10/18/2007 17:43
Admitted 10/19/2007 10:46
Admitted 10/19/2007 21:13
Entered 10/22/2007 10:33
Admitted 10/22/2007 18:51

Thanks very much!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Delete Rows that fit a certain criteria
Here is a macro that works. I was a little more channeleging than I
originally expected.


Sub delete_info()

Dim MyDate As Date
Dim NextDate As Date

RowCount = 1
First = True
Do While Range("A" & RowCount) < ""
MyDate = Range("B" & RowCount)
NextDate = Range("B" & (RowCount + 1))
If MyDate < NextDate Then
last = True
Else
last = False
End If

If (First = False) And (last = False) Then
Rows(RowCount).Delete
Else
RowCount = RowCount + 1
End If

If RowCount = 1 Then
First = True
Else
MyDate = Range("B" & RowCount)
LastDate = Range("B" & (RowCount - 1))
If MyDate < LastDate Then
First = True
Else
First = False
End If
End If

Loop
End Sub


"jpittari" wrote:

We have a security log that tracks, on a daily basis, each time a user enters
our office suite using their security badge. What we are looking for is a
way to remove all the rows from this spreadsheet except for the rows which
contain the first and last time per day the user accessed a badge reader.

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

What we wouuld like to get in the end would be:

Admitted 10/17/2007 12:23
Admitted 10/17/2007 18:31
Entered 10/18/2007 10:13
Entered 10/18/2007 17:43
Admitted 10/19/2007 10:46
Admitted 10/19/2007 21:13
Entered 10/22/2007 10:33
Admitted 10/22/2007 18:51

Thanks very much!

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Delete rows on a criteria [email protected] Excel Programming 2 August 8th 07 08:37 PM
Delete rows based on certain criteria Coal Miner Excel Discussion (Misc queries) 2 March 3rd 06 05:56 PM
Delete rows with different criteria John Excel Programming 7 July 13th 05 05:38 PM
delete rows with criteria S.E. Excel Programming 5 September 9th 04 04:04 PM
Delete rows w/o criteria RickK[_2_] Excel Programming 2 October 31st 03 04:48 PM


All times are GMT +1. The time now is 02:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement