Combine two named ranges into a 3rd named range

[mc-lemons]

Hi all,

Please first have a look at this link so that you may follow me:
Merge two columns into one list in excel | Get Digital Help - Microsoft Excel resource

I would like to combine List1 and List2 into a 3^rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don’t want to use Column C like in the example shown in the link above).

Here'e the formula from the example:

=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))

I've played around with it, but could not come with any that worked.

Any help is greatly appreciated.

Thanks for looking!

 

[Aladin Akyurek]

Hi all,

Please first have a look at this link so that you may follow me:
Merge two columns into one list in excel | Get Digital Help - Microsoft Excel resource

I would like to combine List1 and List2 into a 3^rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don’t want to use Column C like in the example shown in the link above).

Here'e the formula from the example:

=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))

I've played around with it, but could not come with any that worked.

Any help is greatly appreciated.

Thanks for looking!

If you add the ARRAYUNION function, written in VBA, to your workbook, you can have:

List3, defined as referreing to:

=ARRAYUNION(List1,List2)

This is a horizontal array/vector; the following will be vertical...

=TRANSPOSE(ARRAYUNION(List1,List2))

Here is ArrayUnion if interested:

________________________
Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function
________________________

 

[mc-lemons]

Aldain,

Thanks for sharing! Thats a nice little UDF.

As many times is my problem, I am not sure if all users can use VBA, so i was looking for a non-VBA approach. (I forgot to mention that in the original post)

Thanks again for the help.

 

[Aladin Akyurek]

Aldain,

Thanks for sharing! Thats a nice little UDF.

As many times is my problem, I am not sure if all users can use VBA, so i was looking for a non-VBA approach. (I forgot to mention that in the original post)

Thanks again for the help.

I am aware of the request. This is, while VBA, a functional solution.