Slow Array formula or is there any alternative

[SHAWN]

Hi,

I'm working on a report, where I have a column of values ("List 1")
and I need to do a lookup against some other lookup table ("W2Ord").
Then, when all the lookups are done, the formula continues and looks
for values that are not found in the "List 1" field, but found in the
"W2Ord".

I had troubles figuring out how to accomplish this without VBA and
Harlan Grove has kindly offered a solution to my problem, which is an
array formula, that looks like this:

{=IF(NOT(ISERROR(VLOOKUP(A6,W2Ord,1,FALSE))),
VLOOKUP(A6,W2Ord,1,FALSE),IF(A6="",INDEX(W2Ord,MATCH(0,COUNTIF(C$5:C5,W2Ord),0)),""))}

Basically, it says if a value is found in the W2Ord table, then do a
lookup, otherwise, if there is no values in column A, look at all
values above and bring me a value from W2Ord, that doesn't have a
match in List 1.

List 1 List 2(this is a calculated field - the array formula)
123 123
126 --->blank since no value exists in W2Ord
128 128
134 ----> this value was found in W2Ord but no in List
1
135 .....etc etc

The formula works fine and does what I need. The problem is that there
are about 300 to 500 values in List 1 and about 1500 to 2000 in the
lookup table (W2Ord).
When I copy the formula down, the lookups work nice and fast. It's
when it starts looking for "no matches" it gets really slow and after
600-700 th record literally takes forever to calculate.

I tried to solve the problem programatically - looping copying 50
records at a time down and then pasting them special as values to
avoid recalculation of a large number of formulas - to no avail - I
would stop the macro after 25 minutes and it would still be in the
900th record range. I tried copy/paste formulas down, filling formulas
down, - nothing would work. I've read somewhere that array formulas
might not be a very good solution for this kinda job but can't think
of anything as an alternative.

Can anybody help me here.

TIA.

 

[Tom Ogilvy]

Does it need to be dynamic - or do you just need to assemble/create the
list. For the latter

You could put a dummy column next to the second list

use
=if(countif(first list,first cell in second list),"Match","No Match")

then drag this down the column

Do an autofilter on these two columns (Data=>Filter=>Autofilter)

On the dummy column in the dropdown select No Match

Select the second list items an copy the cells (only the visible cells will
be copied)

Paste at the bottom of the report list.

Use the Vlookup part of your formula to populate the top of the list.

 

[Stan Scott]

Shawn,

First off, there's no need to enter this as an array formula. Make it into
a regular formula,using the Edit key, and then copy it down the column.
There are probably other ways to make this more efficient, but this is the
first one that springs to mind.

Stan Scott
New York City

 

[Tom Ogilvy]

the second argument of Countif is a Range, so I believe it would need to be
array entered to accomplish the intended purpose.

Original thread:
http://groups.google.com/[email protected]

 

[Harlan Grove]

the second argument of Countif is a Range, so I believe it would need to be
array entered to accomplish the intended purpose.

....

It needs to be array-entered not because of COUNTIF per se, which returns
arrays when its *second* argument is a single area range or an array, but
because of MATCH which, unlike SUMPRODUCT and LOOKUP, can't cope with
dynamic arrays as second arguments unless it's in an array formula.

 

[Harlan Grove]

I had troubles figuring out how to accomplish this without VBA and
Harlan Grove has kindly offered a solution to my problem, which is an
array formula, that looks like this:

{=IF(NOT(ISERROR(VLOOKUP(A6,W2Ord,1,FALSE))),
VLOOKUP(A6,W2Ord,1,FALSE),IF(A6="",INDEX(W2Ord,
MATCH(0,COUNTIF(C$5:C5,W2Ord),0)),""))}

....

That's not my formula.

You've screwed it up with VLOOKUPs that I didn't use. Note that if the
initial VLOOKUP in the IF condition doesn't return an error, THERE'S
ABSOLUTELY NO BENEFIT FROM CALLING IT A SECOND TIME! In other words,

=IF(ISNUMBER(VLOOKUP(A6,W2Ord,1,0)),A6,...)

because nonerror VLOOKUP(x,y,1,0) == x by necessity.

*IF* your W2Ord list contained only distinct numeric values sorted in
ascending order, you could use (assuming the status entry corresponding to
A6 should be in B6)

B6:
=IF(ISNUMBER(A6),IF(A6<MIN(W2Ord),"",IF(VLOOKUP(A6,W2Ord,1)=A6,A6,"")),
IF(B5<MAX(W2Ord),INDEX(W2Ord,MATCH(B5,W2Ord)+1),""))

filled down. This could be made more efficient by calculating MIN(W2Ord) and
MAX(W2Ord) in other cells and referring to those cells rather than calling
MIN and MAX in each col B formula.

If W2Ord isn't sorted or contains duplicate numeric values which shouldn't
be duplicated in the status column in the report, then the only improvement
you may be able to achieve would be something like

B6 [*array* formula]:
=IF(ISNUMBER(A6),IF(A6<MIN(W2Ord),"",IF(ISNUMBER(MATCH(A6,W2Ord,0)),A6,"")),
IF(B5<MAX(W2Ord),MIN(IF(W2Ord>B5,W2Ord)),""))

This assumes ISNUMBER(MATCH(x,y,0)) will recalc faster than COUNTIF(y,x) and
that MIN(IF(y>z,y)) will recalc faster than
INDEX(y,MATCH(0,COUNTIF(.,y),0)).

If you're going to go for a programatic solution, read List2 into an array,
keep track separately of its MIN and MAX values, and sort it. Then loop
through your List1 checking its values against List2's MIN value and using
binary search to find closest matches in List2. Once the final value in
List1 has been matched, dump the rest of the stored and sorted List2 into
the cells below the last matched value. Don't just use any formula in
batches of cells.