Remember Me?

#1
February 27th 07, 07:15 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 7
How to delete a data row based on a condition

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.
--
wmc

#2
February 28th 07, 02:47 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
How to delete a data row based on a condition

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wmc" wrote:
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.
--
wmc

#3
February 28th 07, 11:40 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
How to delete a data row based on a condition

Typo ..

In E,

In C,

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
#4
April 18th 07, 12:16 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 7
How to delete a data row based on a condition

Thanks, Max.

--
wmc

"wmc" wrote:

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.
--
wmc

#5
April 18th 07, 02:40 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
How to delete a data row based on a condition

You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wmc" wrote in message
...
Thanks, Max.

--
wmc

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

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Discussion (Misc queries) 1 December 27th 06 05:47 PM aheintz57 Excel Worksheet Functions 4 October 9th 06 05:12 AM Peanut Excel Discussion (Misc queries) 2 October 2nd 06 09:55 PM [email protected] Excel Worksheet Functions 9 September 4th 06 02:45 PM Christine Excel Worksheet Functions 8 May 4th 06 09:47 AM

All times are GMT +1. The time now is 05:04 AM.