[Solved] Merging two ranges into a single array

[omniyo]

Hi guys,

I have a Calc document with 3 sheets.
- The first of them contains basic data that doesn't reference anything.
- The second one, must contain "A" rows (Range1) from Sheet 1 as colum titles (which i got it working through this: "=TRANSPOSE(RANGE1)" ).
- The third one, must contain "A" rows from Sheet 1 and 2 (Range1 and Range2) as column titles.

I tried plenty of ways with no luck because I don't know how to merge both ranges into a single one.
My last try was trying to create my own formula but it doesn't work:

Code: Select all

Function ArrayUnion(ByVal va1 As Variant, ByVal va2 As Variant) As Variant

	Dim firstArraySize As Integer
	Dim secondArraySize As Integer
	
	firstArraySize = UBound(va1)
	secondArraySize = UBound(va2)
	
	If firstArraySize = -1 Then
		ArrayUnion = va2
	ElseIf secondArraySize = -1 Then
		ArrayUnion = va2
	Else
		finalArraySize = firstArraySize + secondArraySize
		Dim finalArray(finalArraySize - 1)
		Dim i As Integer
		For i = 0 To firstArraySize
			finalArray(i) = va1(i)
		Next i
		Dim j As Integer
		For j = i To finalArraySize
			finalArray(j) = va2(j-firstArraySize)
		Next j
		ArrayUnion = finalArray
	End If
	
End Function

If this code would work I could do something like this:
"=TRANSPOSE(ArrayUnion(RANGE1, RANGE2))"

Any help would be really appreaciated.

Thanks in advance

[JohnSUN-Pensioner]

Firstly, if you apply the function TRANSPOSE() to result of function ArrayUnion(), you will not get the column headings as intended, but one column of values. Will ArrayUnion() return simple array? So it will be single ROW of values.

Code: Select all

Function ArrayUnion(va1 As Variant, _
		Optional va2 As Variant, _
		Optional va3 As Variant, _
		Optional va4 As Variant, _
		Optional va5 As Variant, _
		Optional va6 As Variant, _
		Optional va7 As Variant, _
		Optional va8 As Variant, _
		Optional va9 As Variant, _
		Optional va10 As Variant, _
		Optional va11 As Variant, _
		Optional va12 As Variant) As Variant
Dim lastElement As Long
Dim res() As Variant
	lastElement =  -1
	addParamToArray(va1, res, lastElement)
	If Not IsMissing(va2) Then addParamToArray(va2, res, lastElement)
	If Not IsMissing(va3) Then addParamToArray(va3, res, lastElement)
	If Not IsMissing(va4) Then addParamToArray(va4, res, lastElement)
	If Not IsMissing(va5) Then addParamToArray(va5, res, lastElement)
	If Not IsMissing(va6) Then addParamToArray(va6, res, lastElement)
	If Not IsMissing(va7) Then addParamToArray(va7, res, lastElement)
	If Not IsMissing(va8) Then addParamToArray(va8, res, lastElement)
	If Not IsMissing(va9) Then addParamToArray(va9, res, lastElement)
	If Not IsMissing(va10) Then addParamToArray(va10, res, lastElement)
	If Not IsMissing(va11) Then addParamToArray(va11, res, lastElement)
	If Not IsMissing(va12) Then addParamToArray(va12, res, lastElement)

	If lastElement > 0 Then
		ReDim Preserve res(lastElement)
	Else
		res = Array()
	EndIf
	ArrayUnion = res
End Function

Sub addParamToArray(param As Variant, res  As Variant, lastElement As Long) 
Const NEW_SIZE = 10
Dim i As Long, j As Long, lastSize As Long
	If IsArray(param) Then
		For i = LBound(param) To UBound(param)
			For j = LBound(param, 2) To UBound(param, 2)
				lastElement = lastElement + 1
				If lastElement > UBound(res) Then
					lastSize = UBound(res) + NEW_SIZE
					ReDim Preserve res(lastSize)
				EndIf
				res(lastElement) = param(i, j)
			Next j
		Next i
	Else
		lastElement = lastElement + 1
		If lastElement > UBound(res) Then
			lastSize = UBound(res) + NEW_SIZE
			ReDim Preserve res(lastSize)
		EndIf
		res(lastElement) = param
	EndIf
End Sub

You can call it like as

Code: Select all

{=TRANSPOSE(ARRAYUNION(A1:A18;E1;C1:C7))}
{=ARRAYUNION(A1:A18;C1:C7)}
{=ARRAYUNION("Result:";C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7)} etc

[MrProgrammer]

No evil macro is needed, just a normal formula.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[omniyo]

Hi guys,

Thanks you both, I really like Mr. programmer's solution and i fixed it in that way but now I found a bigger challenge. I need to join three ranges of rows into a separate file as follows:
File1
====
Sheet1:
- s1row1
- s1row2
- s1row3
Sheet2:
- s2row1
- s2row2
- s2row3
Sheet3:
- s3row1
- s3row2
- s3row3

File2
=====
Sheet1:
- s1row1
- s1row2
- s1row3
- s2row1
- s2row2
- s2row3
- s3row1
- s3row2
- s3row3

I tried dde + offset (as a base in order to build another formula) but it didn't work for me:
=OFFSET(DDE("soffice";"path\to\file.ods";"Sheet1.A1:A5"); ROW()-1;0)

Thanks in advance