Define an array

[Charles]

Hello all,

I have data in several rows and not every row has the same
amount of data -

A B C
1 10 12
2 10 11 42
3 15

I need to be able to refer to this data as a single array
of values in a formula. Any thoughts on how I can do that?

I need to pass the array, 10,12,10,11,42,15 to a MATCH
function.

TFYH

 

[Bernie Deitrick]

Charles,

Sorry, but MATCH uses a one-dimensional array: just a column, or just a row.
You will need to use formulas or cut and paste to make a linear array of
those numbers.

HTH,
Bernie
MS Excel MVP

 

[Myrna Larson]

Here's a VBA function that will create a single array, skipping blank cells.

To use with your MATCH funtion, the formula would be

=MATCH(myValue,MakeArray(G2:I4),0)

But remember, MATCH will return the position in the virtual array, not the
position on the worksheet. If the data you showed is in G2:I4, and you use
MATCH to find the value 11 (which is in H3), the result is 4.

Function MakeArray(aRange As Range) As Variant
Dim Ary As Variant
Dim C As Long
Dim P As Long
Dim R As Long
Dim v As Variant

ReDim v(1 To aRange.Cells.Count)
P = 0
Ary = aRange.Value
For R = 1 To UBound(Ary, 1)
For C = 1 To UBound(Ary, 2)
If IsEmpty(Ary(R, C)) = False Then
P = P + 1
v(P) = Ary(R, C)
End If
Next C
Next R
ReDim Preserve v(1 To P)
MakeArray = v
End Function

 

[Charles]

Thank you for your help.

I pretty much know how MATCH works but how would I use a
formula to create a linear array from my sample data? I
tried using variations of TRANSPOSE but nothing worked. I
also tried using a dynamic defined range name for each row
but couldn't get the ranges to concatenate properly into a
single linear array.

Any further help in this matter will ge greatly
appreciated.

 

[Frank Kabel]

Hi
AFAIK not really possible with formulas alone. This would require VBA
to create a single column array first

 

[Charles]

Thank you, Myrna.

I would prefer a non-VBA solution but if it's not possible
then I guess my choices are limited.

 

[Bernie Deitrick]

Charles,

Perhaps it would be better to describe what your final result would be - why
do you want to pass the array to MATCH, and what do you hope your final
function will provide?

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Frank,

Time to change your signature ;-)

Regards
Frank Kabel Microsoft Excel MVP
Frankfurt, Germany

Bernie
MS Excel MVP

 

[Charles]

I'm trying to extract unique values.

From my sample data:

A B C
1 10 12
2 10 11 42
3 15

The result would be (in either a row or column, it doesn't
matter and that's not a problem):

10 12 11 42 15

I was able to do this easily when I copy/pasted the values
into a single column array but that's not really desirable.

I also tried using Myrna's UDF but it's returning ALL the
values , not just the unique values. I still may be able
to get that to work with some more work.

Thanks for your help.

 

[Myrna Larson]

It's not possible with worksheet functions.

NOW you say you want unique values??? In your original post, your example was

"I need to pass the array, 10,12,10,11,42,15"

10 appears twice in your list!

Modify the code as shown below to eliminate duplicates:

Function MakeArray(aRange As Range) As Variant
Dim Ary As Variant
Dim C As Long
Dim P As Long
Dim R As Long
Dim v As Variant
Dim x As Variant

ReDim v(1 To aRange.Cells.Count)
P = 0
Ary = aRange.Value
For R = 1 To UBound(Ary, 1)
For C = 1 To UBound(Ary, 2)
x = Ary(R, C)
If IsEmpty(x) = False Then
If IsError(Application.Match(x, v, 0)) Then
P = P + 1
v(P) = x
End If
End If
Next C
Next R
ReDim Preserve v(1 To P)
MakeArray = v
End Function

 

[Frank Kabel]

nice!!

 

[Charles]

Thank you, Myrna.

That solved the problem and at the same time requires a
considerably less complex formula since the dupes are
removed in the UDF. Fantastic!

Here's the formula that I am now able to use:

=INDEX(makearray(B$1:K$5),SMALL(MATCH(makearray
($B$1:$K$5),makearray($B$1:$K$5),0),ROW(1:1)))

Charles

 

[Myrna Larson]

Glad it was useful.

Thank you, Myrna.

That solved the problem and at the same time requires a
considerably less complex formula since the dupes are
removed in the UDF. Fantastic!

Here's the formula that I am now able to use:

=INDEX(makearray(B$1:K$5),SMALL(MATCH(makearray
($B$1:$K$5),makearray($B$1:$K$5),0),ROW(1:1)))

Charles

 

[Frank Kabel]

Hi Charles
simplyfying your formula
=INDEX(makearray(B$1:K$5),ROW(1:1))

this should do as well

 

[Myrna Larson]

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?

 

[Frank Kabel]

Hi Myrna
I forgot to add that the OP has top copy this formula down for as many
rows as required

I also thought about entering a multi-cell array formula but as you
mentiones this is probelmatic as you don't know the number of returned
values. So maybe I'd use the non array variant:
=IF(ISERROR(INDEX(makearray(B$1:K$5),ROW(1:1))),"",INDEX(makearray(B$1:
K$5),ROW(1:1)))
and copy this down

 

[Charles]

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

 

[Myrna Larson]

I don't know what else is going on in your worksheet, but I expect the
MakeArray function will win no awards for speed: for one thing, it calls
MATCH for every non-blank cell in the range. And, since I didn't know what
sort of data might be in the range, I used Variant variables rather than,
say, long integers or doubles.

With Frank's latest formula, you have to call the function once for every
cell in the range, and a 2nd time for each non-unique value. If it's a
100-cell range with all values unique, that would be 200 calls to get the
100 values. If there are only 10 unique values, you would call it 110 times.
But that's the best you can do when the number of entries in the new array
isn't known.

A more efficient solution (WRT calculation speed) would be to write the
procedure as a SUB with 2 arguments: the source range (same as now), and a
2nd range consisting of one cell, namely the 1st cell in which to write the
output. The SUB could automatically write all values in a column, starting
at the specified cell. The SUB could be called by a Worksheet_Change event
macro or by the Calculate event macro. But if the address of the source
range changes from day to day, and there's no way to specify a maximum
possible range, that may not be workable, either.