Re: Slow Array formula or is there any alternative
From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 07/24/04
- Next message: Tom Ogilvy: "Re: Excel VBA INPUTBOX() - Please Help"
- Previous message: Stan Scott: "Re: Slow Array formula or is there any alternative"
- In reply to: Stan Scott: "Re: Slow Array formula or is there any alternative"
- Next in thread: Harlan Grove: "Re: Slow Array formula or is there any alternative"
- Reply: Harlan Grove: "Re: Slow Array formula or is there any alternative"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Jul 2004 23:56:20 -0400
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/groups?threadm=6c5f60c1.0407161639.10cb02ec%40posting.google.com
--
Regards,
Tom Ogilvy
"Stan Scott" <stanss@bellatlantic.net> wrote in message
news:OlkbI%23ScEHA.1644@tk2msftngp13.phx.gbl...
> 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
>
> "SHAWN" <valglad@mail.com> wrote in message
> news:6c5f60c1.0407231642.4847013d@posting.google.com...
> > 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.
>
>
- Next message: Tom Ogilvy: "Re: Excel VBA INPUTBOX() - Please Help"
- Previous message: Stan Scott: "Re: Slow Array formula or is there any alternative"
- In reply to: Stan Scott: "Re: Slow Array formula or is there any alternative"
- Next in thread: Harlan Grove: "Re: Slow Array formula or is there any alternative"
- Reply: Harlan Grove: "Re: Slow Array formula or is there any alternative"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
