Delete Rows that fit a certain criteria

[jpittari]

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!

 

[Tom Hutchins]

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

 

[Tom Hutchins]

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" & LastRow&)
Range("D2" & LastRow&).Select
Columns("D").Select
Columns("D").Copy
Columns("D").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("D2").Select
Range("A1" & 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" & 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

 

[Joel]

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