Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 27th 07, 07:15 PM posted to microsoft.public.excel.newusers
wmc wmc is offline
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 7
Default 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.
Thanks in advance for responding!
--
wmc

  #2   Report Post  
Old February 28th 07, 02:47 PM posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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.
Thanks in advance for responding!
--
wmc

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

Typo ..

In E,


should have read as:

In C,


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

Thanks, Max.

Your instructions have been extremely helpful
--
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.
Thanks in advance for responding!
--
wmc

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

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

Your instructions have been extremely helpful
--
wmc





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look [email protected] Excel Discussion (Misc queries) 1 December 27th 06 05:47 PM
Copying data based on a condition aheintz57 Excel Worksheet Functions 4 October 9th 06 05:12 AM
Is there a way to delete a cell value based on a condition? Peanut Excel Discussion (Misc queries) 2 October 2nd 06 09:55 PM
copy data from 1 worksheet to another based on a condition [email protected] Excel Worksheet Functions 9 September 4th 06 02:45 PM
Can I delete an entire row if condition is not met? Christine Excel Worksheet Functions 8 May 4th 06 09:47 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017