listing active campaigns

[Sethrow]

Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2. The important data is column A has name column B has start date and column C has end date.

I want sheet1 to list only the campaigns that are active, ie where the current date is between the start date and the end date.

Anyone got any ideas?

Also I want it to update everytime I open the file, is this possible? If so how?

Many thanks

Seth

P.So There is more to this problem but I think if get this bit then I should be alble to get the rest working! But don't be surprised if there are follow up questions lol

 

[NBVC]

First add a helper column next to the last columnn in Sheet 2,

Assuming that your first row contains column titles and the table is current in A1:C1000, then in the first cell of row 2, enter:

=IF(AND(B2<=TODAY(),C2>=TODAY()),"Ok","")

, copy that down (you can hide this column if desired).

Then in sheet 1, Cell A2, enter:

=IF(ROW(Sheet2!$D2)-ROW(Sheet2!$D$1)>COUNTIF(Sheet2!$D$1:$D$1000,"Ok"),"",INDEX(Sheet2!A$1:A$1000,SMALL(IF(Sheet2!$D$1:$D$1000="Ok",ROW(Sheet2!$D$1:$D$1000)),ROW(Sheet2!$D2)-ROW(Sheet2!$D$1))))

Remember, to adjust ranges to suit (i.e. replace 1000, with the number of records you have)

Confirm this formula with Ctrl+Shift+Enter, not just Enter and then copy it down as far as your list in Sheet2 is long.

These formulas will self adjust according to current date.

Note: If you want to also pull the corresponding start and end dates, then just copy the big formula over to the next columns. You may have to format those date columns as Date.

 

[Aladin Akyurek]

Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2.

...

How long?

 

[Sethrow]

Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2.

...

How long?

About 1000

Guess its not really that long in excell terms but its long for me when looking thru it by hand.

 

[Aladin Akyurek]

Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2.

...

How long?

About 1000

Guess its not really that long in excell terms but its long for me when looking thru it by hand.

Sheet2 (source)

ExtractActiveEventList.xls
	A	B	C	D
1	12/4/2005			0
2	Event	Sdate	Edate	Idx
3	event-1	3-Jan-05	19-Jan-05
4	event-13	4-Jan-05	14-Jan-05
5	event-16	4-Jan-05	6-Jan-05
6	event-987	2-May-05	18-May-05
7	event-988	2-May-05	3-May-05
8	event-989	2-May-05	4-May-05
9	event-1022	3-May-05	9-May-05
10	event-1023	3-May-05	25-May-05
11	event-1024	3-May-05	24-May-05
12	event-1025	4-May-05	7-May-05
13	event-1026	4-May-05	12-May-05
Sheet2

A1:

=TODAY()

D1 must house a 0.

D3, copied down:

=IF(A3<>"",IF((B3<=$A$1)*(C3>=$A$1),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,""),"")

Sheet1 (destination)

ExtractActiveEventList.xls
	A	B	C	D	E
1	8
2	Pos	Event	Sdate	Edate
3	159	event-2869	11/10/2005	12/5/2005
4	163	event-2873	11/11/2005	12/6/2005
5	170	event-2880	11/11/2005	12/4/2005
6	185	event-2895	11/14/2005	12/8/2005
7	186	event-2896	11/14/2005	12/8/2005
8	194	event-2904	11/14/2005	12/6/2005
9	195	event-2905	11/14/2005	12/6/2005
10	196	event-2906	11/14/2005	12/5/2005
11
Sheet1

A1:

=LOOKUP(9.99999999999999E+307,Sheet2!D:D)

A3, copied down:

=IF(ROWS($A$3:A3)<=$A$1,MATCH(ROWS($A$3:A3),Sheet2!$D:$D),"")

B3, copied across to D3 then down:

=IF(N($A3),INDEX(Sheet2!A:A,$A3),"")

It would be nice if someone would provide code which, triggered by a change in A1, would copy the formulas in A3:D3 A1 times downwards.

 

[Sethrow]

Wow impressive work

Great work NBVC and Aladin Akyurek!! Both your methods appear to work, I dunno which works better yet but thanks to both of you. I'm gonna be working on this properly very soon and will return with any problems I had with either method as that can only help us all lear.

As Aladin Akyurek has said it would be really useful if someone could come up with code that could automatically fill the column up based on the number in A1.

Thanks again and I'll be back soon once I've had more of a play and thoroughly tested them both.

Cheers,

Seth

 

[Aladin Akyurek]

Re: Wow impressive work

Great work NBVC and Aladin Akyurek!! Both your methods appear to work, I dunno which works better yet but thanks to both of you. I'm gonna be working on this properly very soon and will return with any problems I had with either method as that can only help us all lear.

As Aladin Akyurek has said it would be really useful if someone could come up with code that could automatically fill the column up based on the number in A1.

Thanks again and I'll be back soon once I've had more of a play and thoroughly tested them both.

Cheers,

Seth

You're welcome.

In case anyone wonders the difference is in speed.

 

[PaddyD]

Why not just use an advanced filter with the appropriate criteria set up 7 copy the results somewhere else:

Book1
	A	B	C	D	E	F	G
1	Campaign	Start	End
2	a	1/01/2001	2/01/2002		FALSE
3	b	1/01/2005	1/01/2006
4	c	1/01/2006	1/01/2007		Campaign	Start	End
5	d	1/01/2006			b	1/01/2005	1/01/2006
6	e	1/01/2005	5/12/2005		e	1/01/2005	5/12/2005
7	f				g	5/12/2005	5/12/2005
8	g	5/12/2005	5/12/2005
9					Database
10					Criteria
11					Results
Sheet1

 

[Sethrow]

Confirm this formula with Ctrl+Shift+Enter, not just Enter and then copy it down as far as your list in Sheet2 is long.

What exactly does confirming with Ctrl+Shift+Enter do?

 

[NBVC]

Look at this topic for the answer to that interesting question:

http://www.mrexcel.com/board2/viewtopic.php?t=177388&highlight=cse