List based on a date
 

I am looking for a function or code to extract from a list , names of those
persons whose anuual fee payment due date is within next 15 days.This output
list be a report or in a separate sheet which always reflect current status
of the above function.
Example :sheet 1
r/no -- col a------------col b----------------------------col c
1 ---name----------membership date--------------annual fee
2. ----david----------09-feb-2003--------------------$60
3. ------mark----------08-jan-2001--------------------$50
4. ------rosy-----------*********--------------------***
5. ------cary-----------05-jan-2004---------------------$45
6. ------james---------11-nov-2005---------------------$80
(note: all in the list are not necessarily members)

What I need is:(as on today)
r/no-----col a--------------col b
1.-------name-------------Due date
2.-------cary--------------05-jan-2006
3.-------mark-------------08-jan-2006

List based on a date
 

One play, using non-array formulas ..

Sample construct at:
http://www.savefile.com/files/3653714
ListBasedOnDueDates_Tungana_misc.xls

Assume table below is in Sheet1, cols A to C,
data from row2 down (dates in col B)

name membership date annual fee
david 09-Feb-03 $60
mark 08-Jan-01 $50
rosy ********* ***
cary 05-Jan-04 $45
james 11-Nov-05 $80
Peter 31-Dec-05 $50
James 05-Jan-04 $80
etc

(Possibility of duplicate membership dates
is also assumed. This is likely.)

In a new Sheet2,
Enter labels in A1:B1 : Name, Due Date

Put in

A2:
=IF(ISERROR(SMALL($F:$F,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F, 0)))

B2:
=IF(ISERROR(SMALL($F:$F,ROW(A1))),"",
INDEX(D:D,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))

D2:
=IF(ISNUMBER(Sheet1!B2),IF(TODAY()DATE(YEAR(TODAY ()),MONTH(Sheet1!B2),DAY(S
heet1!B2)),DATE(YEAR(TODAY())+1,MONTH(Sheet1!B2),D AY(Sheet1!B2)),DATE(YEAR(T
ODAY()),MONTH(Sheet1!B2),DAY(Sheet1!B2))),"")

E2: =IF(D2="","",D2-TODAY())
F2: =IF(OR(E2="",E215),"",E2+ROW()/10^10)

(Leave F1 empty)

(Cols D to F are helper cols)

Select A1:F1, copy down to say, F50,
to cover the max expected extent of data in Sheet1

Format col B (& col D if desired) as dates

Cols A & B will return the required results

Names with duplicate due dates, if any,
will appear in the same relative order as in Sheet1

For the sample data, we'd get:

Name Due Date
Peter 31-Dec-05
cary 05-Jan-06
James 05-Jan-06
mark 08-Jan-06
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TUNGANA KURMA RAJU" wrote in
message ...
I am looking for a function or code to extract from a list , names of
those
persons whose anuual fee payment due date is within next 15 days.This
output
list be a report or in a separate sheet which always reflect current
status
of the above function.
Example :sheet 1
r/no -- col a------------col b----------------------------col c
1 ---name----------membership date--------------annual fee
2. ----david----------09-feb-2003--------------------$60
3. ------mark----------08-jan-2001--------------------$50
4. ------rosy-----------*********--------------------***
5. ------cary-----------05-jan-2004---------------------$45
6. ------james---------11-nov-2005---------------------$80
(note: all in the list are not necessarily members)

What I need is:(as on today)
r/no-----col a--------------col b
1.-------name-------------Due date
2.-------cary--------------05-jan-2006
3.-------mark-------------08-jan-2006