Array Formula taking too Long

**gborja888** · 04-01-2012, 02:50 AM

I have been creating an EXCEL macros to get the maximum values of 5 data fields with two column search criteria. I have already tried to simplify to optimize the macros, however I still find this method to be slow and taking too long to complete and are considering using VB instead to handle this. Any suggestions on how to handle this in VB. TIA.

My data looks something like below:

Column C Column D Column E Column F Column G Column H Column I
1
2 Max Max Max Max Max
3 CFName LPARName PathBusy ChgCnt %TotDely SyncST AsyncST
4 BA01C7 CP1A 20 0 0.1 20 180
5 CP1B 0 0 0 10 200
6 CP1C 0 0 0 15 130
7 CP1D 0 0 0 30 200
8 CP1E 0 0 0 10 150
9 CP1F 0 0 0 12 100
10 BA02C7 CP1A 20 0 0.1 20 180
11 CP1B 0 0 0 10 200
12 CP1C 0 0 0 15 130
13 CP1D 0 0 0 30 200
CP1E 0 0 0 10 150
CP1F 0 0 0 12 100
BCC1C1 CP1A 20 0 0.1 20 180
CP1B 0 0 0 10 200
CP1C 0 0 0 15 130
CP1D 0 0 0 30 200
CP1E 0 0 0 10 150
CP1F 0 0 0 12 100
BCC1C2 CP1A 20 0 0.1 20 180
CP1B 0 0 0 10 200
CP1C 0 0 0 15 130
CP1D 0 0 0 30 200
CP1E 0 0 0 10 150
27 CP1F 0 0 0 12 100

My search criteria are Column C and D to find the max values for Column E to I

I used the Array formula for the following range:

for Range("E4:E27") is {=MAX(IF((CF1OUT!C:C=$C$4)*(CF1OUT!D:D=D4),CF1OUT!L:L))}
F4:F27 {=MAX(IF((CF1OUT!C:C=$C$4)*(CF1OUT!D:D=D4),CF1OUT!M:M))}
G4:G27 {=MAX(IF((CF1OUT!C:C=$C$4)*(CF1OUT!D:D=D4),CF1OUT!X:X))}
H4:H27 {=MAX(IF((CF1OUT!C:C=$C$4)*(CF1OUT!D:D=D4),CF1OUT!Q:Q))}
I4:I27 {=MAX(IF((CF1OUT!C:C=$C$4)*(CF1OUT!D:D=D4),CF1OUT!R:R))}

I have attached herewith the sample output worksheet and data.

**gborja888** · 04-01-2012, 03:00 AM

Here's the table again:

ColumnC ColumnD ColumnE ColumnF ColumnG ColumnH ColumnI
1
2 Max Max Max Max Max
3 CFName LPARName PathBusy ChgCnt %TotDely SyncST AsyncST
4 BA01C7 CP1A 20 0 0.1 20 180
5 CP1B 0 0 0 10 200
6 CP1C 0 0 0 15 130
7 CP1D 0 0 0 30 200
8 CP1E 0 0 0 10 150
9 CP1F 0 0 0 12 100
10 BA02C7 CP1A 20 0 0.1 20 180
11 CP1B 0 0 0 10 200
12 CP1C 0 0 0 15 130
13 CP1D 0 0 0 30 200
14 CP1E 0 0 0 10 150
15 CP1F 0 0 0 12 100
16 BCC1C1 CP1A 20 0 0.1 20 180
17 CP1B 0 0 0 10 200
18 CP1C 0 0 0 15 130
19 CP1D 0 0 0 30 200
20 CP1E 0 0 0 10 150
21 CP1F 0 0 0 12 100
22 BCC1C2 CP1A 20 0 0.1 20 180
23 CP1B 0 0 0 10 200
24 CP1C 0 0 0 15 130
25 CP1D 0 0 0 30 200
26 CP1E 0 0 0 10 150
27 CP1F 0 0 0 12 100

**DonkeyOte** · 04-01-2012, 03:03 AM

Arrays are inefficient given they are iterative by nature and do not restrict themselves to used range intersect of precedent ranges (unlike most other functions)

If you change C:C to say C1: C1000 you will notice a significant improvement.

If you require for the range to be dynamic (based on data added/removed) you can use a Dynamic Named Range, however, use INDEX in construct rather than OFFSET (IMO to avoid Volatility of latter)

Example of the above

First, Insert a new Name as follows:

Name: _Data
RefersTo: =CF1OUT!$A$1:INDEX(CF1OUT!$A:$IV,MATCH(9.99E+307,CF1OUT!$A:$A),MATCH(REPT("Z",255),CF1OUT!$1:$1))

Modify your Array to use the Dynamic Range, e.g

MAIN!E4:
=MAX(IF((INDEX(_Data,0,3)=LOOKUP(REPT("Z",255),$C$4:$C4))*(INDEX(_Data,0,4)=$D4),INDEX(_Data,0,12)))
confirmed with CTRL + SHIFT + ENTER

MAIN!F4:
=MAX(IF((INDEX(_Data,0,3)=LOOKUP(REPT("Z",255),$C$4:$C4))*(INDEX(_Data,0,4)=$D4),INDEX(_Data,0,13)))
confirmed with CTRL + SHIFT + ENTER

MAIN!G4:
=MAX(IF((INDEX(_Data,0,3)=LOOKUP(REPT("Z",255),$C$4:$C4))*(INDEX(_Data,0,4)=D4),INDEX(_Data,0,24)))
confirmed with CTRL + SHIFT + ENTER

MAIN!H4:
=MAX(IF((INDEX(_Data,0,3)=$C$4)*(INDEX(_Data,0,4)=D4),INDEX(_Data,0,17)))
confirmed with CTRL + SHIFT + ENTER

MAIN!I4:
=MAX(IF((INDEX(_Data,0,3)=$C$4)*(INDEX(_Data,0,4)=D4),INDEX(_Data,0,18)))
confirmed with CTRL + SHIFT + ENTER

Copy E4:I4 down to row 27

**gborja888** · 04-01-2012, 03:10 AM

Hi DonkeyOte,

Thanks for your prompt reply. Let me try your suggestion..

gborja888

**nilem** · 04-01-2012, 03:14 AM

also try, for example, in the E4 array formula

=MAX(IF((Table1[CFNAME]=$C$4)*(Table1[SYSID]=D4),Table1[Pthbsy]))

**DonkeyOte** · 04-01-2012, 03:18 AM

nilem, TABLE1 currently spans 1m+ rows so this is no different to using entire column cell references in the Array (i.e. you would want to resize Table first).

**nilem** · 04-01-2012, 03:36 AM

Oops, I have not looked at that the TABLE1 has a million rows of empty. Sorry

**gborja888** · 04-01-2012, 03:41 AM

DonkeyOte, it's working much faster now.. I have to convert them to macro. thanks for your help.. I really appreciate it.

Array Formula taking too Long

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Array Formula taking too Long

Re: Array Formula taking too Long

Re: Array Formula taking too Long

Re: Array Formula taking too Long

Re: Array Formula taking too Long

Re: Array Formula taking too Long

Re: Array Formula taking too Long

Re: Array Formula taking too Long

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions