Worksheet equivalent of VBA Union?
 

Tom

Hopefully I am not overlooking the obvious.

I think u r...

name kolA = a1:a10
name kolB = b1:b10
name kolAB = =(kolA;kolB)
name kolABC = (a1:a10;b1:b10;c1:c10)


works in formulas that accept multiarea ranges



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

=CHOOSE({1,2},MyRangeColA,MyRangeColC)

Entered as an array formula is about as close as I could come. This
builds a two dimensional array, not really a union.

Worksheet equivalent of VBA Union?
 

Tom, keepITcool,
Thanks for your replies. Tom, your CHOOSE approach is new to me and I
just know I'm going to find it useful in the future. But I am looking
to reference a range and keepITcool's method seems to do it.

So, to extend my example (again, in the Immediate Window):

Names.Add "MyRangeColAuC","=(MyRangeColA,MyRangeColC)"
Names.Add "MyRangeColAuCuB","=(MyRangeColAC,MyRangeColB) "

? Names("MyRangeColAuC").RefersToRange.Address
$A$1:$A$3,$C$1:$C$3
? Names("MyRangeColAuCuB").RefersToRange.Address
$A$1:$A$3,$C$1:$C$3,$B$1:$B$3

Curiously, however, MyRangeColAuCuB now has one column and three rows:

? Names("MyRangeColAuCuB").RefersToRange.Columns.Cou nt
1
? Names("MyRangeColAuCuB").RefersToRange.Rows.Count
3

Also, although the ranges were defined as columns A, C, B in that
order, the resulting range has been rendered as A, B, C and the
columns and rows oriented correctly:

For c=1 To 3 : _
For r=1 To 3 : _
?"(" & r & "," & c & ")=" & _
Names("MyRangeColAuCuB").RefersToRange(r,c).Addres s : _
next : _
next
(1,1)=$A$1
(2,1)=$A$2
(3,1)=$A$3
(1,2)=$B$1
(2,2)=$B$2
(3,2)=$B$3
(1,3)=$C$1
(2,3)=$C$2
(3,3)=$C$3

These shouldn't present me with any problems but I'd appreciate it if
someone could tell me how these transformations came about.

Many thanks.

"Tom Ogilvy" wrote in message ...
I didn't interpret that as the requirement. I agree the "," in US, ";" in
some other areas is the union operator. Such as
Sum(a1:A10,B1:B10) which doesn't require array entry - but you may well be
right.

--
Regards,
Tom Ogilvy

keepitcool wrote in message
...

Tom

Hopefully I am not overlooking the obvious.

I think u r...

name kolA = a1:a10
name kolB = b1:b10
name kolAB = =(kolA;kolB)
name kolABC = (a1:a10;b1:b10;c1:c10)


works in formulas that accept multiarea ranges



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

=CHOOSE({1,2},MyRangeColA,MyRangeColC)

Entered as an array formula is about as close as I could come. This
builds a two dimensional array, not really a union.

Worksheet equivalent of VBA Union?
 

Thanks Tom, I get it now.

"Tom Ogilvy" wrote in message ...
Actually, the transformations were not made

Your range actually has three areas.

? Names("MyRangeColAuCuB").RefersToRange.Areas.Count
3

When you do Columns.count, the results defaults to just the first area

? Range("$A$1:$A$3,$C$1:$C$3,$B$1:$B$3").Columns.cou nt
1

Even if you make the areas adjacent - as specified, there are still three
areas.
? Range("$A$1:$A$3,$B$1:$B$3,$C$1:$C$3").Columns.cou nt
1


When you do your loop, the r and c in the formula
Names("MyRangeColAuCuB").RefersToRange(r,c).Addres s

Are relative to the top left cell in the first area. (A1)

? Names("MyRangeColAuCuB").RefersToRange(5,10).Addre ss
$J$5

so the ranges have not been reorganized.

--
Regards,
Tom Ogilvy


"onedaywhen" wrote in message
om...
Tom, keepITcool,
Thanks for your replies. Tom, your CHOOSE approach is new to me and I
just know I'm going to find it useful in the future. But I am looking
to reference a range and keepITcool's method seems to do it.

So, to extend my example (again, in the Immediate Window):

Names.Add "MyRangeColAuC","=(MyRangeColA,MyRangeColC)"
Names.Add "MyRangeColAuCuB","=(MyRangeColAC,MyRangeColB) "

? Names("MyRangeColAuC").RefersToRange.Address
$A$1:$A$3,$C$1:$C$3
? Names("MyRangeColAuCuB").RefersToRange.Address
$A$1:$A$3,$C$1:$C$3,$B$1:$B$3

Curiously, however, MyRangeColAuCuB now has one column and three rows:

? Names("MyRangeColAuCuB").RefersToRange.Columns.Cou nt
1
? Names("MyRangeColAuCuB").RefersToRange.Rows.Count
3

Also, although the ranges were defined as columns A, C, B in that
order, the resulting range has been rendered as A, B, C and the
columns and rows oriented correctly:

For c=1 To 3 : _
For r=1 To 3 : _
?"(" & r & "," & c & ")=" & _
Names("MyRangeColAuCuB").RefersToRange(r,c).Addres s : _
next : _
next
(1,1)=$A$1
(2,1)=$A$2
(3,1)=$A$3
(1,2)=$B$1
(2,2)=$B$2
(3,2)=$B$3
(1,3)=$C$1
(2,3)=$C$2
(3,3)=$C$3

These shouldn't present me with any problems but I'd appreciate it if
someone could tell me how these transformations came about.

Many thanks.

"Tom Ogilvy" wrote in message
...
I didn't interpret that as the requirement. I agree the "," in US, ";"
in
some other areas is the union operator. Such as
Sum(a1:A10,B1:B10) which doesn't require array entry - but you may well
be
right.

--
Regards,
Tom Ogilvy

keepitcool wrote in message
...

Tom

Hopefully I am not overlooking the obvious.

I think u r...

name kolA = a1:a10
name kolB = b1:b10
name kolAB = =(kolA;kolB)
name kolABC = (a1:a10;b1:b10;c1:c10)


works in formulas that accept multiarea ranges



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

=CHOOSE({1,2},MyRangeColA,MyRangeColC)

Entered as an array formula is about as close as I could come. This
builds a two dimensional array, not really a union.