Re: Define an array

From: Charles (anonymous_at_discussions.microsoft.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 11:51:14 -0700

Yes, I'm still following this thread.

The goal was to extract ALL unique values from a multi-row
multi-column range. I could do that easily if the the
values were in a single column or row. I could not figure
out how to create a linear array out of the range so
that's when I posted. I was/am able to extract the unique
values with a formula or an advanced filter but needed to
figure out how to create/pass the linear array to the
match function (with dupes).

The range can hold up to hundreds of values and varies
greatly day to day. Since your modified UDF now eliminates
duplicates I no longer need to do that in the formula.

I can see that Frank's simplified formula will do nicely.

So, all I needed help on was how to create the linear
array. The rest I am capable of doing.

Thanks to everyone for their help and input.

Charles
 
>-----Original Message-----
>Hi, Frank:
>
>Thanks for figuring out what he is "up to". I didn't have
the time to spend on
>it, but I mentally questioned whether the formula
accomplished anything other
>than returning the 1st value from the virtual array.
>
>Charles' formula looked to me to be terribly inefficient -
- he was calling the
>MakeArray function 3 (or more?) times. If that were
really necessary, I think
>it would have been better to find out the ultimate
purpose of all this, then
>code the whole thing in VBA, where the array could have
been saved for re-use
>instead of calling the function repeatedly.
>
>If he uses ROW(1:1) because the formula will be copied
down, then optimally
>this should be entered as an array formula from the
outset, but the problem
>here is that the number of cells isn't known at that
point. If errors are
>acceptable, one could select the maximum size (50 cells
for B1:K5) then use
>=TRANSPOSE(MakeArray(B$1:K$5)). That would handle varying
numbers of unique
>values in the range, but not a change in the range size
itself, i.e. to
>B$2:L$10, etc.
>
>Charles -- are you still reading this thread? If so, I'm
curious. What is this
>formula supposed to do?
>
>On Thu, 7 Oct 2004 07:33:43 +0200, "Frank Kabel"
<frank.kabel@freenet.de>
>wrote:
>
>>Hi Charles
>>simplyfying your formula :-)
>>=INDEX(makearray(B$1:K$5),ROW(1:1))
>>
>>this should do as well
>
>.
>