Matrix

[garnote]

Hi all,

I have two matix named «One» and «Two»
One : ( 3 rows and 1 column )
1
2
3
Two : ( 5 rows and 1 column )
4
5
6
7
8
With the names of the matrix, how is possible generate
another matrix like this :
Three : ( 8 rows and 1 column )
1
2
3
4
5
6
7
8
Number of rows in each matrix are variables !

Thanks
Serge

 

[JE McGimpsey]

not sure what you mean by "matrix" - a range of cells? I'm assuming not
VBA since you posted in worksheet.functions.

If so, you can create a named range containing both using the union
operator (,)

Choose Insert/Name/Define

Name in Workbook: Three
Refers To: =One,Two

then you can use the combined range:

=SUM(Three) ==> 36
=COUNT(Three) ==> 8

but Three won't act as a continuous range, so functions like

=SUMPRODUCT(Three, B1:B8)

will fail.

If I've missed the mark, post back.

 

[garnote]

Yep !
In french, union is ;

Thank you very much

Serge

 

[Alan Beban]

If the OP needs a continuous range, and the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to his
workbook, he can use

Name in Worbook: Three
Refers To: =Transpose(MakeArray(One,Two,1))

Alan Beban

 

[garnote]

It's really curious, when I use
Name : Three
Reference : =One;Two
I can use the name Three for a graphic
but I can't see the matrix Three in my worsheet ?
{=Three} don't work !!!

Serge

 

[JE McGimpsey]

it doesn't work in the same way that

=B1:B3;D15

array-entered into F1:F8 doesn't work - it simply joins them into the
named range.

You'll need to use a macro (or the add-in that Alan Beban wrote) to use
the joined range as a continuous array.

 

[Harlan Grove]

You'll need to use a macro (or the add-in that Alan Beban wrote)
to use the joined range as a continuous array.

....

Not necessarily. If both arrays are single column, multiple rows, then
hardcoding is one possible way to do this, e.g., entering a multiple cell
array formula like

=INDEX(Three,{1;2;3;1;2;3;4;5},0,{1;1;1;2;2;2;2;2})

This could be generalized as

=INDEX(Three,SeqThree-(SeqThree>ROWS(INDEX(Three,0,1,1)))
*ROWS(INDEX(Three,0,1,1)),1,1+(SeqThree>ROWS(INDEX(Three,0,1,1))))

where the extra name SeqThree is defined as

=ROW(INDIRECT("1:"&(ROWS(INDEX(Rng_1_2,0,1,1))
+ROWS(INDEX(Rng_1_2,0,1,1)))))

As for udfs, Alan's MakeArray is overkill for converting arbitrary ranges
into 1D arrays and too restrictive to be completely general. For more
generality, see

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


If this is only needed for ranges, simpler is better.

Function rtoa(rng As Range) As Variant
Dim n As Long, rv() As Variant, c As Range

ReDim rv(1 To rng.Cells.Count, 1 To 1)

For Each c In rng
n = n + 1
rv(n, 1) = c.Value
Next c

rtoa = rv
End Function

 

[JE McGimpsey]

Very nice! I had forgotten all about that syntax (and I doubt I could
have come up with the generalization if I had remembered!).

 

[Alan Beban]

If this is only needed for ranges, simpler is better.

Function rtoa(rng As Range) As Variant
Dim n As Long, rv() As Variant, c As Range

ReDim rv(1 To rng.Cells.Count, 1 To 1)

For Each c In rng
n = n + 1
rv(n, 1) = c.Value
Next c

rtoa = rv
End Function

To get it to work I had to change it to

Function rtoa(ByVal rng As Range) As Variant or
Function rtoa(rng)As Variant

Alan Beban

 

[Harlan Grove]

To get it to work I had to change it to

Function rtoa(ByVal rng As Range) As Variant or
Function rtoa(rng)As Variant

Simply put, this shouldn't be necessary. Objects such as Ranges are always
passed by reference, though the reference could, I suppose, be a second
level reference or a stack location holding the reference.

I'm using XL2000 to test this, and using my original function, along with
=ROW() in each cell of A1:A20, the formula =rtoa((A8:A20,A1:A7)) returns
{8;9;10;11;12;13;14;15;16;17;18;19;20;1;2;3;4;5;6;7} as expected. What do
your function calls look like? And the ever popular question, what do you
mean by 'to get it to work'? That is, what type of error was the original
giving you?

 

[Alan Beban]

Sub test3001()
Set rng1 = Range("a1:b1,d1:e1")
Dim arr()
arr = rtoa(rng1)
End Sub

Compile error: ByRef argument type mismatch

Alan Beban

 

[Alan Beban]

Another way to avoid the problem is to declare rng1 explicitly as Range.

Alan Beban