Delete Range Cell Based on Condition

[Jean]

Hi,

This is a sample of the sheet i am working on:

2012088552 300 04/27/2007 16:53:14
2012091284 300 04/28/2007 19:41:34
2012421319 300 04/25/2007 11:52:00
2012817722 300 04/28/2007 12:49:36
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59
I want to keep all rows where the date is 04/27/2007 and delete all the
other rows
EXCEPT the ones that have the same value in the first column as the value
where the date is 04/27/2007. This means the result would be:

2012088552 300 04/27/2007 16:53:14
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59

Any help is greatly appreciated!

 

[Dave Peterson]

I put your data in A27 (id, qty, date, time) and headers in row 1.

Then I inserted a new column (E) and put this array formula:
=OR(C2=DATE(2007,4,27),
ISNUMBER(MATCH(1,((A2=A2:A7)*(C2:C7=DATE(2007,4,27))),0)))

(all one line and change a7 and c7 to the last row you need)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Then drag it down the range.

Then apply Data|filter|autofilter to column E
filter to show the false
delete those visible rows
delete column E.

 

[JE McGimpsey]

One way:

You don't say whether the time is included with the date in a column or
whether it's a separate column, so I'll assume the date/time is a single
column.

Public Sub FourTwentySeven()
Const cdDate As Date = #4/27/2007#
Dim colKeys As Collection
Dim rFound As Range
Dim rDelete As Range
Dim rCell As Range
Dim nFirstRow As Long
Dim i As Long
Dim bRetain As Boolean

With Columns(3).Cells
Set rFound = .Find( _
What:=Format(cdDate, "mm/dd/yyyy"), _
After:=.Cells(.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
nFirstRow = rFound.Row
Set colKeys = New Collection
Do
colKeys.Add rFound.Offset(0, -2).Value
Set rFound = .FindNext(after:=rFound)
Loop Until rFound.Row = nFirstRow
End If
End With
If Not colKeys Is Nothing Then
For Each rCell In Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
bRetain = False
For i = 1 To colKeys.Count
If rCell.Value = colKeys(i) Then
bRetain = True
Exit For
End If
Next i
If Not bRetain Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End If
End Sub

 

[Jean]

Thanks a lot!! Actually the time is in the same row, but time does not matter
just the date, maybe i should change the type to just date then after running
the code change it back to custom Date and Time?

Thanks!

 

[Jean]

Hi Dave,

thanks a lot for the help, i am trying the formula but it s returning false
on all rows since my date is in the year/mm/dd TIME format. It is important
that i keep this format, do you know which formula to preserves the format?

Thanks!

 

[Jean]

Dave,

2012088552 300 04/27/2007 16:53:14
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59

The formula is returning false for the raw
2013558438 300 04/25/2007 12:15:59 instead of true, I need this row
even if the Date is different then 04/27/2007 since the id is equal to the ID
in the 3rd row where date is 04/27/2007, what do you think? Many Thanks!

 

[Dave Peterson]

So there are only 3 columns in your data--Column C contains both the date and
time.

If column C contains a real date/time (not just text that looks like a
date/time), you could try this formula:

=OR((TEXT(C2,"yyyymmdd")="20070427"),
ISNUMBER(MATCH(1,((A2=A2:A7)*(TEXT(C2:C7,"yyyymmdd")="20070427")),0)))

 

[Jean]

Hi JE, your code works, thanks a lot! But actually i am also trying to figure
out how to include time in the code since it is important for the result
meaning, for a given day, let's say 4/27/2007, i have the following rows:

2012088552 300 04/27/2007 16:53:14
2012091284 300 04/27/2007 19:41:34
2012421284 300 05/01/2007 20:52:00
2012817722 300 04/28/2007 12:49:36
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59

After running your code i would have the following results:
2012088552 300 04/27/2007 16:53:14
2012091284 300 04/27/2007 19:41:34
2012421284 300 05/01/2007 20:52:00
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59

What i would like to have is:
2012088552 300 04/27/2007 16:53:14
2012091284 300 04/27/2007 19:41:34

2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59

The row with the date 5/1/2007 20:52:00 is also deleted (even if it has the
same account # in the first column) since the difference between the date is
more than 3 days (more then 72 hours) so basically i like to keep the
following:

1. All the rows with the date 4/27/2007
2. All the rows with the same account# (first column) as the account #s with
the date 4/27/2007 but limited to 72 hours prior to 4/27/2007 or 72 hours
after 4/27/2007

Please let me know if you have a solution to that! THANKS SO MUCH!!!!