Creating a list dependant on values

[Yuee]

I am trying to create a database for a fitness centre that will create a list of people whose membership has expired or is about to expire.

I have created a formula so that it will subtract their membership end date from the current date, which will give the number of days remaining in their membership. Thus everyone with a negative number has already expired.

My question is how do I get it to search through the whole database and create a list of everyone who has expired (negative number) on a new worksheet?

I imagine this may be a relatively easy formula, but it's got me stumped.

If you could help that would be greatly appreciated.

Cheers,
Yuee

 

[fairwinds]

Welcome to the board!

Try advanced filter.

E1:E2 is the criteria range.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	end date	exp		exp		Name	end date	exp
2	MR1	2005-11-20	-5		<0		MR1	2005-11-20	-5
3	MR2	2005-11-21	-4				MR2	2005-11-21	-4
4	MR3	2005-11-22	-3				MR3	2005-11-22	-3
5	MR4	2005-11-23	-2				MR4	2005-11-23	-2
6	MR5	2005-11-24	-1				MR5	2005-11-24	-1
7	MR6	2005-11-25	0
8	MR7	2005-11-26	1
9	MR8	2005-11-27	2
10	MR9	2005-11-28	3
11	MR10	2005-11-29	4
12	MR11	2005-11-30	5
13	MR12	2005-12-01	6
14	MR13	2005-12-02	7
15	MR14	2005-12-03	8
16	MR15	2005-12-04	9
17
Sheet1

 

[Barrie Davidson]

Welcome to MrExcel!

You could also use AUTOFILTER, filter the list for expired dates, and then print the list. Have you used AUTOFILTER?

 

[Yuee]

Thanks for the warm welcome...but most of all for the help.

I have used auto filter before, but not the advanced filter. This pretty much solves my problem.

Thanks again.

Cheers,
Yuee

 

[Yuee]

Sorry, 1 more question.

With the advanced filter how would i write the criteria if I want it to list out the people that will expire within the next 31 days...ie. How do I get it look for everyone with >0 days left and everyone with <31 days?

 

[fairwinds]

Change criteria range to:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	end date	exp		exp	exp		Name	end date	exp
2	MR1	2005-11-20	-6		>0	<31		MR8	2005-11-27	1
3	MR2	2005-11-21	-5					MR9	2005-11-28	2
4	MR3	2005-11-22	-4					MR10	2005-11-29	3
5	MR4	2005-11-23	-3					MR11	2005-11-30	4
6	MR5	2005-11-24	-2					MR12	2005-12-01	5
7	MR6	2005-11-25	-1					MR13	2005-12-02	6
8	MR7	2005-11-26	0					MR14	2005-12-03	7
9	MR8	2005-11-27	1
10	MR9	2005-11-28	2
11	MR10	2005-11-29	3
12	MR11	2005-11-30	4
13	MR12	2005-12-01	5
14	MR13	2005-12-02	6
15	MR14	2005-12-03	7
16	MR15	2006-12-04	373
Sheet1

 

[Yuee]

Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee

 

[Yogi Anand]

Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee

Hi Yuee:

As you begin to work on it ... you will find there are several ways of doing what you want to accomplish. Following is an illustration of using Conditional Formatting that highlites all the Member names whose membership has expired as of TODAY (the current day) ...

Book2
	A	B	C	D
1	Name	end
2	MR1	11/20/2005
3	MR2	11/21/2005
4	MR3	11/22/2005
5	MR4	11/23/2005
6	MR5	11/24/2005
7	MR6	11/25/2005
8	MR7	11/26/2005
9	MR8	11/27/2005
10	MR9	11/28/2005
11	MR10	11/29/2005
12	MR11	11/30/2005
13	MR12	12/01/2005
14	MR13	12/02/2005
15	MR14	12/03/2005
16	MR15	12/04/2006
Sheet13 (2)

I have used Conditional Formatting for cell B2 ...

formula is ... =$B2-TODAY()<0 ... color Yellow

Now this highliting would automatically adjust every day as TODAY date changes. Now you act on the highlited list of names every day.

I f you need to incorporate features like highliting those whose membership has been expired for 7 days, or more than 30 days, ... you can do that as well.

 

[Peter_SSs]

Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee

This has 2 lists, 'Expired' and 'Next 31 days' (meaning will expire in the next 31 days). The lists could be combined into 1 if desired.

1. C2 (copied down) is: =B2-TODAY()
2. F1 (counts how many have expired) is: =COUNTIF($C$2:$C$16,"<=0")
3. I1 (counts how many will expire in the next 31 days) is: =COUNTIF($C$2:$C$16,"<=31")-COUNTIF($C$2:$C$16,"<=0")
4. E2 (copied down): =IF(ROW()>1+$F$1,"",INDEX($A$1:$A$16,SUMPRODUCT(SMALL(--($C$2:$C$16<=0)*ROW($C$2:$C$16),-1+ROW()+COUNTIF($C$2:$C$16,">0")))))
5. H2 (copied down): =IF(ROW()>1+$I$1,"",INDEX($A$1:$A$16,SUMPRODUCT(SMALL(--($C$2:$C$16>0)*--($C$2:$C$16<=31)*ROW($C$2:$C$16),-1+ROW()+COUNTIF($C$2:$C$16,"<=0")+COUNTIF($C$2:$C$16,">31")))))

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	Name	end date	exp		Expired	6		Next 31 days	5
2	MR1	20/11/2005	-6		MR1			MR4
3	MR2	21/11/2005	-5		MR2			MR8
4	MR3	22/11/2005	-4		MR3			MR9
5	MR4	23/12/2005	27		MR5			MR10
6	MR5	24/11/2005	-2		MR6			MR14
7	MR6	25/11/2005	-1		MR7
8	MR7	26/11/2005	0
9	MR8	27/11/2005	1
10	MR9	28/11/2005	2
11	MR10	29/11/2005	3
12	MR11	14/02/2006	80
13	MR12	15/02/2006	81
14	MR13	16/02/2006	82
15	MR14	3/12/2005	7
16	MR15	18/05/2006	173
Expired Membership

 

[Aladin Akyurek]

Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee

Book7
	A	B	C	D	E	F
1	2005-11-26		0		7
2	name	end date	idx		Expired	By
3	MR1	2005-11-20			MR8	1
4	MR2	2005-11-21			MR9	2
5	MR3	2005-11-22			MR10	3
6	MR4	2005-11-23			MR11	4
7	MR5	2005-11-24			MR12	5
8	MR6	2005-11-25			MR13	6
9	MR7	2005-11-26			MR14	7
10	MR8	2005-11-27	1
11	MR9	2005-11-28	2
12	MR10	2005-11-29	3
13	MR11	2005-11-30	4
14	MR12	2005-12-01	5
15	MR13	2005-12-02	6
16	MR14	2005-12-03	7
17	MR15	2006-12-04
Sheet1

Formulas...

A1:

=TODAY()

C1 must house a 0.

C3, copied down:

=IF(B3<>"",IF((B3-$A$1>0)*(B3-$A$1<=31),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,""),"")

If you want any expiration ( >0 ), remove the *(B3-$A$1<=31) bit.

E1:

=LOOKUP(9.99999999999999E+307,C1:C17)

E3, copied down:

=IF(ROW()-ROW(E$3)+1<=$E$1,LOOKUP(ROW()-ROW(E$3)+1,$C$3:$C$17,$A$3:$A$17),"")

If so desired/needed...

F3, copied down:

=IF(E3<>"",LOOKUP(ROW()-ROW(F$3)+1,$C$3:$C$17,$B$3:$B$17)-$A$1,"")

If you are on Excel 2003, all this can be made to run automatically to the full extend.