How to delete a data row based on a condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wmc - 27 Feb 2007 19:15 GMT

I am using Excel (Excel 2003 on Windows XP) to track due dates that are
based on a certain event date (e.g. 30days, 100days, 120days, and 180days
from the event date) and had no problem determining each due date by formula.
The due dates appear in columns N,O,P,Q, the event date is in column B.
Columns C to M contain text and numbers (names, dates of birth, attained age,
a prior event in date format, file numbers, etc.).
Each row represents a new record for a different name with either the same
or a different event date (which means that two rows may have the same due
dates). Here's what I would like to achieve: for Excel to remove each row
from the worksheet once the due date for the last report in that row has
passed at the 181 day mark. Also, if at all possible, I would like for
Excel to move all data contained in that row to a new worksheet where the
data will be tracked further with 1year and 2year endpoints. Hopefully,
someone out there understands my layman's language.
Thanks in advance for responding!

Signature

wmc

Reply to this Message

Max - 28 Feb 2007 14:47 GMT

Try this formulas play which delivers the desired equivalent results

Source data assumed entered in sheet: X, in cols A to Q from row2 down,
with the key col = col Q which contain the "180days" dates (real dates)

In a new sheet named as say: E
(E=Expired cases)

Put in A2:
=IF(X!Q2="","",IF(TODAY()>X!Q2,ROW(),""))
Leave A1 blank

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$A,ROW(A1))))
Copy B2 to R2. Select A2:R2, copy down to cover the max expected extent of
data in X, say down to N100. Format cols C, O to R as dates, etc. Cols B to R
returns the lines from X which have expired (dates > 180 days), with all
lines neatly bunched at the top.

Then just make a copy of E, name it as say: C
(C=Current)

In E,

Replace the formula in A2 with:
=IF(X!Q2="","",IF(TODAY()<=X!Q2,ROW(),""))
Then copy A2 down to A100. Leave the rest unchanged. Cols B to R returns the
lines from X which are still current (ie dates <= 180 days), with all lines
neatly bunched at the top.

Lines in X will hence dynamically transfer to E or C
depending on the dates in col Q, as desired.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I am using Excel (Excel 2003 on Windows XP) to track due dates that are
> based on a certain event date (e.g. 30days, 100days, 120days, and 180days
[quoted text clipped - 11 lines]
> someone out there understands my layman's language.
> Thanks in advance for responding!

Reply to this Message

Max - 28 Feb 2007 23:40 GMT

Typo ..

> In E,

should have read as:

> In C,

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

wmc - 18 Apr 2007 00:16 GMT

Thanks, Max.

Your instructions have been extremely helpful

Signature