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 wrote:

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" wrote:

I put your data in A2:D7 (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.



Jean wrote:

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


--

Dave Peterson