# Is code more efficient than my formula

#### Agent86

##### Board Regular
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
ABCDEFG
2
3From:12/1/2003
4To:12/15/2003
5NameRegion1
6AR1AR2TOT
7Bob112
8Bill011
9Mary101
10Peter011
11Ron112
12
13
Sheet1
km_biweekly_analysis_12-05-2003-test3.xls
ABCD
1NameItemWeekEndingDate
2BobAR112/1/2003
3BillAR212/2/2003
4MaryAR112/3/2003
5PeterAR212/4/2003
6RonAR112/5/2003
7BobAR212/6/2003
8BillAR112/20/2003
9MaryAR212/23/2003
10PeterAR212/24/2003
11RonAR112/25/2003
12BobAR211/28/2003
13BillAR211/28/2003
14MaryAR111/28/2003
15PeterAR211/28/2003
16RonAR212/15/2003
17
Sheet2

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### parry

##### MrExcel MVP
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.

##### MrExcel MVP
Can you sort the data on the date column?

#### Agent86

##### Board Regular

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

##### Legend
Book1.xls
ABCD
1NameItemWeekEndingDateConcatenated
2BobAR211/28/2003BobAR2
3BillAR211/28/2003BillAR2
4MaryAR111/28/2003MaryAR1
5PeterAR211/28/2003PeterAR2
6BobAR112/1/2003BobAR1
7BillAR212/2/2003BillAR2
8MaryAR112/3/2003MaryAR1
9PeterAR212/4/2003PeterAR2
10RonAR112/5/2003RonAR1
11BobAR212/6/2003BobAR2
12RonAR212/15/2003RonAR2
13BillAR112/20/2003BillAR1
14MaryAR212/23/2003MaryAR2
15PeterAR212/24/2003PeterAR2
16RonAR112/25/2003RonAR1
Sheet2

you can then use something like --
Book1.xls
ABCDEF
1NameAR1AR2TotalStartDtStopDt
2Bob11212/1/200312/15/2003
3Bill011612
4Mary101
5Peter011
6Ron112
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

##### Board Regular
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

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

#### just_jon

##### Legend
pretty cheesey work around in E3 --
Book1.xls
ABCDEF
1NameAR1AR2TotalStartDtStopDt
2Bob12312/12/200112/15/2003
3Bill022212
4Mary202
5Peter022
6Ron112
Sheet3

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

#### Agent86

##### Board Regular
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

##### Legend
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)

Replies
12
Views
620
Replies
0
Views
526
Replies
2
Views
311
Replies
3
Views
156
Replies
5
Views
160

1,195,992
Messages
6,012,741
Members
441,724
Latest member
Aalbid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back