CombineTwoArrays Function

[olasa]

I have tried to find a Function that can combine two Arrays into one.

Something like =CombineTwoArrays(Array1,Array2)
Array1 is a 1:n, Array2 is also 1:n
CombineTwoArrays is 2:n

Is there a simple way to do this?

[dilipandey]

Hi olasa,

By combining two arrays, what do you actually wanted to achieve, please share more. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[dangelor]

Take a look at http://www.cpearson.com/Excel/VBAArrays.htm

[Domenic]

Is this what you mean?

Sub test()
    Dim Arr1() As Variant
    Dim Arr2() As Variant
    Dim Arr3() As Variant
    Arr1 = Array("A", "B", "C")
    Arr2 = Array(200, 400, 500)
    Arr3 = CombineTwoArrays(Arr1, Arr2)
End Sub

Function CombineTwoArrays(Array1() As Variant, Array2() As Variant) As Variant
    Dim Array3() As Variant
    Dim Array4() As Variant
    Dim i As Long
    Dim j As Long
    Array3 = Array(Array1, Array2)
    ReDim Array4(UBound(Array3), UBound(Array3(LBound(Array3))))
    For i = LBound(Array3) To UBound(Array3)
        For j = LBound(Array3(i)) To UBound(Array3(i))
            Array4(i, j) = Array3(i)(j)
        Next j
    Next i
    CombineTwoArrays = Array4
End Function

[snb]

to add the values in 2 arrays (ranges) D1:G3 and D7:G9

Sub snb5()
[D1:G3].Offset(20) = [index(D1:G3 +D7:G9,)]
End Sub

you can use this UDF

Function snb7(c01 As Range, c02 As Range)
  snb7 = Evaluate("index(" & c01.Address & "+" & c02.Address & ",)")
End Function

You will have to enter this UDF as an arrayformula: ctrl-shift-enter

{=snb7(D1:G3,D7:G9)}
and autofill the adjacent cells correponding to the range's size

[olasa]

Hello everyone,

I appreciate all the replies but no success (see attached).
It looks like you have already given me a solution and - I am sure it's something simple - but I just can't get it to work.
Any Aha suggestions?

//Ola


---
I have this formula =ScatterChart(G34:H38;;;;;;;;;;;;;;;1841892) in another (big file)*.
Here I would like to replace G34:H38 with the new Function.
My x-y values comes from various rows in a large spreadsheet, but 'Sparklines for Excel' want (unfortunatelly) a continous region.

I have also tried
http://www.cpearson.com/excel/VBAArrays.htm (could not get it to work)
http://www.dailydoseofexcel.com/arch...bining-arrays/ (not working)

[WHER]

Maybe too simplistic, but couldn't you use the index-function?

[olasa]

Yes it's too simple for a matrix of ScatterCharts (see encl. picture).

The idea was to use a Function =CombineTwoArrays(Product1, Product2) that draws the data directly from the DataTable (row19).
To avoid to go via a ChartData table (row30) - where the number of combinations grows exponetially (messy).
'Sparklines for Excel' want a continous area (see function Fx row)... otherwise it would have been (too) easy.

//Ola

[snb]

@wher

I think you are right:

=INDEX($D$5:$G$7;2*(ROW(1:3)-1)+1;) 

see the attachment.

[olasa]

snb & wher, sorry but your suggestions are roughly what I already have (see the Book1.xlsm attachement).

The 'Sparkline for Excel' function =scatterchart(xyarea,,,) function want one continous area.
I need a single cell formula that can pass the whole xyarea (array) as one continouse area in one single cell. i.e. join row C20:G20 & C23:G23
I think I can only be done by a UDF, but do surprice me.
//Ola

[snb]

you can union 2 ranges and make it a named range and use that in your formula.

Sub snb()
  thisworkbook.names.add "snb2",union(Range("C20:G20"),range("C23:G23"))
End Sub

function =scatterchart(snb2,,,) 

[olasa]

snb, sorry but I just can't get anything to work.
I've tried all suggestions and I the closest I got was =0 otherwise i just get =#REF! or =#VALUE!

[snb]

You can use either:

Sub snb_4()
 ThisWorkbook.Names.Add "snb_qq", Union(Sheet1.Range("D5:G5"), Sheet1.Range("D7:G7"))
End Sub

or

Sub snb_5()
 ThisWorkbook.Names.Add "snb_qq", Application.Index(Sheet1.Range("D5:G7"), Application.Transpose(Array(1, 3)), Array(1, 2, 3, 4))
End Sub

you can use the named range snb_qq in your formula. ( the only thing to prevent is using a 'reseved name', that's why snb2, snb7 doesn't work in E2007/E2010 because that is being reserved for a column).