Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up within date range
I am looking for a formula that I can use to look up rows that have a certain
criteria, and is outside a certain date range. Ex. I want to find all rows that contain Plans 43Z1C1, 43Z2C1, 43Z4C1, & 43ZLC1 and are outside the date range of 12/01/1995 - 12/31/1998 The rows are set up similar to below: POLICY CLAIM PLAN EFF DTE INCURRED -------------------------------------------------------------------- HL01429 532000 49Z4F1 3/19/1999 3/19/1999 HL01431 910700 45Q4A1 3/27/1999 4/8/2009 HL01429 701800 47Z4A1 4/1/1999 10/2/2006 HL01432 910602 47Z4A1 5/25/1999 11/14/2008 HL01432 804906 49Z4A1 5/28/1999 5/7/2006 HL01435 629000 49Z4A1 8/1/1999 3/1/2006 There are about 500 rows, and would only like to find the rows meeting the criteria similar to about so i can move them to another location. Thank You |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up within date range
Easiest way is proabbly to setup a helper column, and then autofilter on that
column. I'm assuming colum D is the date you are concerned with regarding date. Formula for helper column is: =AND(OR(C2="43Z1C1",C2="43Z2C1",C2="43Z4C1",C2="43 ZLC1"),D2<DATEVALUE("12/01/1995"),D2DATEVALUE("12/31/1998")) Copy this formula down as needed, then do an Autofilter sort for "TRUE". These are all the rows that meet your crtieria. You can now select them, and copy them to another location. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jason K" wrote: I am looking for a formula that I can use to look up rows that have a certain criteria, and is outside a certain date range. Ex. I want to find all rows that contain Plans 43Z1C1, 43Z2C1, 43Z4C1, & 43ZLC1 and are outside the date range of 12/01/1995 - 12/31/1998 The rows are set up similar to below: POLICY CLAIM PLAN EFF DTE INCURRED -------------------------------------------------------------------- HL01429 532000 49Z4F1 3/19/1999 3/19/1999 HL01431 910700 45Q4A1 3/27/1999 4/8/2009 HL01429 701800 47Z4A1 4/1/1999 10/2/2006 HL01432 910602 47Z4A1 5/25/1999 11/14/2008 HL01432 804906 49Z4A1 5/28/1999 5/7/2006 HL01435 629000 49Z4A1 8/1/1999 3/1/2006 There are about 500 rows, and would only like to find the rows meeting the criteria similar to about so i can move them to another location. Thank You |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up within date range
Thank you, this works great.
But is there a way to maybe use a range that includes the planes. Because somtimes I have alot of plans that need to be checked. "Jason K" wrote: I am looking for a formula that I can use to look up rows that have a certain criteria, and is outside a certain date range. Ex. I want to find all rows that contain Plans 43Z1C1, 43Z2C1, 43Z4C1, & 43ZLC1 and are outside the date range of 12/01/1995 - 12/31/1998 The rows are set up similar to below: POLICY CLAIM PLAN EFF DTE INCURRED -------------------------------------------------------------------- HL01429 532000 49Z4F1 3/19/1999 3/19/1999 HL01431 910700 45Q4A1 3/27/1999 4/8/2009 HL01429 701800 47Z4A1 4/1/1999 10/2/2006 HL01432 910602 47Z4A1 5/25/1999 11/14/2008 HL01432 804906 49Z4A1 5/28/1999 5/7/2006 HL01435 629000 49Z4A1 8/1/1999 3/1/2006 There are about 500 rows, and would only like to find the rows meeting the criteria similar to about so i can move them to another location. Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum If range of dates date range, sum totals | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |