Is code more efficient than my formula

[Agent86]

I'm using the formulas rather than code to accomplish what I'm trying to do. Basically, I'm picking up data from another sheet and counting the number of times that a particular name shows up within a specified date range. Furthermore, it counts the number of times that the above occurs, but seperates the count depending on a third variable - that being whether the names that fall within the date range have a designation of either "AR1" or "AR2".

My problem is that even though the formulas work, they cause the sheet to calculate very slowly. This further concerns me because the final version will need to have about 250 rows on Sheet1, and 1000's of rows on Sheet2.

Is there code that could be written that would be more efficient, and yet achieve the same outcome?

Below are conceptual drafts of sheets 1 and 2 ... thank you for any direction you can provide me.

km_biweekly_analysis_12-05-2003-test3.xls
	A	B	C	D	E	F	G
2
3					From:	12/1/2003
4					To:	12/15/2003
5	Name	Region1
6		AR1	AR2	TOT
7	Bob	1	1	2
8	Bill	0	1	1
9	Mary	1	0	1
10	Peter	0	1	1
11	Ron	1	1	2
12
13
Sheet1

km_biweekly_analysis_12-05-2003-test3.xls
	A	B	C	D
1	Name	Item	WeekEndingDate
2	Bob	AR1	12/1/2003
3	Bill	AR2	12/2/2003
4	Mary	AR1	12/3/2003
5	Peter	AR2	12/4/2003
6	Ron	AR1	12/5/2003
7	Bob	AR2	12/6/2003
8	Bill	AR1	12/20/2003
9	Mary	AR2	12/23/2003
10	Peter	AR2	12/24/2003
11	Ron	AR1	12/25/2003
12	Bob	AR2	11/28/2003
13	Bill	AR2	11/28/2003
14	Mary	AR1	11/28/2003
15	Peter	AR2	11/28/2003
16	Ron	AR2	12/15/2003
17
Sheet2

 

[parry]

Hi, have you thought about using a pivot table? That would probably be the best option in my opinion.

This could be done with a sub procedure but you have to think about its construction as a formula will always change if the source changes where a prcoedure wont update unless there is code to check the source data. This can be a bit messy. You can create custom functions (code formulas) but I dont think these would match Excels native formulas for speed.

 

[Aladin Akyurek]

Can you sort the data on the date column?

 

[Agent86]

Thank you parry and Aladin for your responses.

parry, I'm kind of new to all this and I've only used pivot tables once before. I'll look into your suggestion and see what I can come up with.

Aladin, "yes", the dates can be sorted. What is it that you're thinking this might do for my problem?

 

[just_jon]

If you can add a column to your table --

Book1.xls
	A	B	C	D
1	Name	Item	WeekEndingDate	Concatenated
2	Bob	AR2	11/28/2003	BobAR2
3	Bill	AR2	11/28/2003	BillAR2
4	Mary	AR1	11/28/2003	MaryAR1
5	Peter	AR2	11/28/2003	PeterAR2
6	Bob	AR1	12/1/2003	BobAR1
7	Bill	AR2	12/2/2003	BillAR2
8	Mary	AR1	12/3/2003	MaryAR1
9	Peter	AR2	12/4/2003	PeterAR2
10	Ron	AR1	12/5/2003	RonAR1
11	Bob	AR2	12/6/2003	BobAR2
12	Ron	AR2	12/15/2003	RonAR2
13	Bill	AR1	12/20/2003	BillAR1
14	Mary	AR2	12/23/2003	MaryAR2
15	Peter	AR2	12/24/2003	PeterAR2
16	Ron	AR1	12/25/2003	RonAR1
Sheet2

you can then use something like --

Book1.xls
	A	B	C	D	E	F
1	Name	AR1	AR2	Total	StartDt	StopDt
2	Bob	1	1	2	12/1/2003	12/15/2003
3	Bill	0	1	1	6	12
4	Mary	1	0	1
5	Peter	0	1	1
6	Ron	1	1	2
Sheet3

in E3: =MATCH(E2,Sheet2!C:C,1)
in F3: =IF(ISNA(E3),"",MATCH(F2,Sheet2!C:C,1))
in B2, copied across and down: =IF(ISNA($E$3),0,COUNTIF(INDEX(Sheet2!$D:$D,$E$3):INDEX(Sheet2!$D:$D,$F$3),"="&$A2&CHAR(127)&B$1))

... all of which Aladin will improve upon, so stay tuned.

 

[Agent86]

Thank you just_jon. Your solution is really fascinating! It gets the results and does so very quickly. I've not used "INDEX" or "MATCH" before, so I need to study those to understand "why" this works.

The only potential problem that I see is if the user enters a date in cell E3 that is an earlier date than exists in Sheet2 Column C. When I do this, Columns B, C and D return all 0's. I'll have to think about that one, but I'm sure there's a solution (there always is!)

Thank you so much again for you kind assistance.

 

[just_jon]

I should have thought of that -- i'll get you a fix in about a minute... want to test.

 

[just_jon]

pretty cheesey work around in E3 --

Book1.xls
	A	B	C	D	E	F
1	Name	AR1	AR2	Total	StartDt	StopDt
2	Bob	1	2	3	12/12/2001	12/15/2003
3	Bill	0	2	2	2	12
4	Mary	2	0	2
5	Peter	0	2	2
6	Ron	1	1	2
Sheet3

EDIT -- Aladin showed this type set up to me; not sure it's right...

 

[Agent86]

Hello, and thank you again just_jon. Something seems to be wrong with your post. Is the HTML turned off? All I can see is the formula for B2.

 

[just_jon]

sorry --

=IF(ISNA(MATCH(E2,Sheet2!C:C,1)),2,MATCH(E2,Sheet2!C:C,1))

and you can revise F3 to: =MATCH(F2,Sheet2!C:C,1)