My question got lost-Formula Help?

[Dan the Man]

The "array" formula you see below works great in terms of calculating what I
need, however it also slows things down significantly which is
understandable. I've also temporarily removed the formula due to the
compromised performance problems it creates on my workbook. My question is
this? Can I create a NEW workbook, and NEW spreadsheet (suggestion below):

Workbook=IDIP Data

Spreadsheet=Requirement Deadliines

Set up a link to the original workbook (IDIP Client Database) and
spreadsheet (New Rule Clients), and bypass my "slowness" problem by having
all the work and effort moved to a different workbook?

I didn't know if this were possible? Is there a formula that could do this,
and if so, do I have to have my current workbook (IDIP Client Database) OPEN
in order for the formula to work and calculate appropriate outcomes in the
NEW workbook?

The current formula I am using on my "New Rule Clients" spreadsheet of my
"IDIP Client Database" Workbook is:

=IF(ISERROR(SMALL(IF(($Z$4:$Z$3500<>"")*($AH$4:$AH$3500="Send IDIP 036 to
DPHS"),ROW($Z$4:$Z$3500),""),ROW($A1))-ROW($A$4)+1),"",INDEX(A$4:A$3500,N(SMALL(IF(($Z$4:$Z$3500<>"")*($AH$4:$AH$3500="Send
IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),ROW($A1))-ROW($A$4)+1)))

Any formula or feedback suggestions would be appreciated?

Thanks in advance,

Dan

 

[Harlan Grove]

The "array" formula you see below works great in terms of calculating
what I need, however it also slows things down significantly which is
understandable. I've also temporarily removed the formula due to the
compromised performance problems it creates on my workbook. My question
is this? Can I create a NEW workbook, and NEW spreadsheet (suggestion
below): ....
Set up a link to the original workbook (IDIP Client Database) and
spreadsheet (New Rule Clients), and bypass my "slowness" problem by
having all the work and effort moved to a different workbook?

If this other workbook would already have done all the work and been saved
with everything just they way you need it AND you'd open it before accessing
these results in it, then, yes, it could speed things up.

I didn't know if this were possible? Is there a formula that could do
this, and if so, do I have to have my current workbook (IDIP Client
Database) OPEN in order for the formula to work and calculate appropriate
outcomes in the NEW workbook?

If recalc speed is as important as you indicate above, external references
into CLOSED workbooks would very likely be MUCH SLOWER than the array
formula you're currently using. So, yes, you'd need to open that workbook.

The current formula I am using on my "New Rule Clients" spreadsheet of
my "IDIP Client Database" Workbook is:

=IF(ISERROR(SMALL(IF(($Z$4:$Z$3500<>"")
*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),
ROW($A1))-ROW($A$4)+1),"",
INDEX(A$4:A$3500,N(SMALL(IF(($Z$4:$Z$3500<>"")
*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),
ROW($A1))-ROW($A$4)+1)))

The main problem here is calling SMALL(<huge array term>,..) twice. And
SMALL isn't exactly a zippy function either. This is an instance in which
you should use two cells for each end result. That is, (using made-up cell
addresses),

X99 [array formula]:
=SMALL(IF(($Z$4:$Z$3500<>"")*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),
ROW($Z$4:$Z$3500),""),ROW($A1))-ROW($A$4)+1

Y99:
=IF(ISNUMBER(X99),INDEX(A$4:A$3500,X99),"")

Since these two formulas are much simpler than your original formula, they
won't take up much more memory, and they should nearly double recalc speed.

But if you're going to use two cells per each result, use them for more
efficient formulas.

X99 [array formula]:
=MATCH(1,($Z$4:$Z$3500<>"")*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),0)

X100 [array formula]:
=MATCH(1,INDEX($Z$4:$Z$3500,X99+1):$Z$3500<>"")
*(INDEX($AH$4:$AH$3500,X99+1):$AH$3500="Send IDIP 036 to DPHS"),0)+X99

Fill X100 down as far as needed. Y99 would remain as above. Not only is
MATCH, at O(N), a decided improvement over SMALL, at O(N*log(N)) *IF* SMALL
uses a quicksort variant or O(N^2) if it uses a bubble sort or shell sort
variant, but the INDEX(a:z,LastMatchIndex+1):z is also shrinking for each
subsequent MATCH call. Faster algorithm on progressively smaller ranges
definitely won't hurt.

The downside, however, is two cells per end result. This is the age-old
speed-storage trade-off. You get to decide which is more important.

 

[Dan the Man]

Thank you for the feedback Harlan. With your suggestions and my original
formulas as a guide, I was able to create a new workbook to place all the
requirements I needed, and achieve the desired results. The speed on my
original workbook isn't compromised as it was when these formulas were
included (as they are housed in a separate workbook), and the only thing I
have to do when I open the new workbook is allow Excel to "link" to where the
data actually is, and "update" the links for current accuracy. Works like a
charm, but my eyes HURT from staring at formula....

Thanks again,

Dan

The "array" formula you see below works great in terms of calculating
what I need, however it also slows things down significantly which is
understandable. I've also temporarily removed the formula due to the
compromised performance problems it creates on my workbook. My question
is this? Can I create a NEW workbook, and NEW spreadsheet (suggestion
below): ....
Set up a link to the original workbook (IDIP Client Database) and
spreadsheet (New Rule Clients), and bypass my "slowness" problem by
having all the work and effort moved to a different workbook?

If this other workbook would already have done all the work and been saved
with everything just they way you need it AND you'd open it before accessing
these results in it, then, yes, it could speed things up.

I didn't know if this were possible? Is there a formula that could do
this, and if so, do I have to have my current workbook (IDIP Client
Database) OPEN in order for the formula to work and calculate appropriate
outcomes in the NEW workbook?

If recalc speed is as important as you indicate above, external references
into CLOSED workbooks would very likely be MUCH SLOWER than the array
formula you're currently using. So, yes, you'd need to open that workbook.

The current formula I am using on my "New Rule Clients" spreadsheet of
my "IDIP Client Database" Workbook is:

=IF(ISERROR(SMALL(IF(($Z$4:$Z$3500<>"")
*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),
ROW($A1))-ROW($A$4)+1),"",
INDEX(A$4:A$3500,N(SMALL(IF(($Z$4:$Z$3500<>"")
*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),
ROW($A1))-ROW($A$4)+1)))

The main problem here is calling SMALL(<huge array term>,..) twice. And
SMALL isn't exactly a zippy function either. This is an instance in which
you should use two cells for each end result. That is, (using made-up cell
addresses),

X99 [array formula]:
=SMALL(IF(($Z$4:$Z$3500<>"")*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),
ROW($Z$4:$Z$3500),""),ROW($A1))-ROW($A$4)+1

Y99:
=IF(ISNUMBER(X99),INDEX(A$4:A$3500,X99),"")

Since these two formulas are much simpler than your original formula, they
won't take up much more memory, and they should nearly double recalc speed.

But if you're going to use two cells per each result, use them for more
efficient formulas.

X99 [array formula]:
=MATCH(1,($Z$4:$Z$3500<>"")*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),0)

X100 [array formula]:
=MATCH(1,INDEX($Z$4:$Z$3500,X99+1):$Z$3500<>"")
*(INDEX($AH$4:$AH$3500,X99+1):$AH$3500="Send IDIP 036 to DPHS"),0)+X99

Fill X100 down as far as needed. Y99 would remain as above. Not only is
MATCH, at O(N), a decided improvement over SMALL, at O(N*log(N)) *IF* SMALL
uses a quicksort variant or O(N^2) if it uses a bubble sort or shell sort
variant, but the INDEX(a:z,LastMatchIndex+1):z is also shrinking for each
subsequent MATCH call. Faster algorithm on progressively smaller ranges
definitely won't hurt.

The downside, however, is two cells per end result. This is the age-old
speed-storage trade-off. You get to decide which is more important.