merging specific column of 3 array in a new array

jxb

Board Regular
To all

The merging of array has been addressed in a few posts but before I write my own (copy someonelse!) code to loop through the arrayI tought I asked here 1st

I have 3 arrays of the same length (n row, 2 column): xarray, yarray, zarray
and the data are numbers something like
0.,0.
0.01,5.2
0.02,6.4
etc

the 1st column is identical for all 3 arrays

I want to create a 4th array MergedData() of length (n row, 4 columns)
where
1st column = 1st column of xarray()
2nd column = 2nd column of xarray()
3rd column = 2nd column of yarray()
4th column = 2nd column of zarray()

What would the "neatest" way of creating the array MergedData()? I can onlu think of something like that!

For i = 0 to UBound(xarray)
MergedData(i,0)=xarray(i,0)
MergedData(i,1)=xarray(i,1)
MergedData(i,2)=yarray(i,1)
MergedData(i,3)=zarray(i,1)
Next

Any suggestions/ideas welcome

Thanks

Regards

JXB

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
To all

The merging of array has been addressed in a few posts but before I write my own (copy someonelse!) code to loop through the arrayI tought I asked here 1st

I have 3 arrays of the same length (n row, 2 column): xarray, yarray, zarray
and the data are numbers something like
0.,0.
0.01,5.2
0.02,6.4
etc

the 1st column is identical for all 3 arrays

I want to create a 4th array MergedData() of length (n row, 4 columns)
where
1st column = 1st column of xarray()
2nd column = 2nd column of xarray()
3rd column = 2nd column of yarray()
4th column = 2nd column of zarray()

What would the "neatest" way of creating the array MergedData()? I can onlu think of something like that!

For i = 0 to UBound(xarray)
MergedData(i,0)=xarray(i,0)
MergedData(i,1)=xarray(i,1)
MergedData(i,2)=yarray(i,1)
MergedData(i,3)=zarray(i,1)
Next

Any suggestions/ideas welcome
Where did the values for these arrays come from... ranges on a worksheet? If so were all the ranges on the same worksheet? If so, then I think we can form the MergedData array directly from the sheet, but we would need to know the address for each of the original ranges and which ones you used to create your xarray, yarray and zarray.

Perhaps something like this:-
Code:
``````[COLOR=Navy]Sub[/COLOR] MG14Sep00
[COLOR=Navy]Dim[/COLOR] rMax [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] R [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Ray
[COLOR=Navy]Dim[/COLOR] Col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
ReDim xarray(1 To 20, 1 To 2)
xarray = Range("A1:B20")

ReDim yarray(1 To 20, 1 To 2)
yarray = Range("C1:D30")

ReDim zarray(1 To 20, 1 To 2)
zarray = Range("E1:F35")
rMax = Application.Max(UBound(xarray), UBound(yarray), UBound(zarray))
ReDim MergedData(1 To rMax, 1 To 4)
Ray = Array(xarray, xarray, yarray, zarray)

[COLOR=Navy]For[/COLOR] n = 0 To 3
[COLOR=Navy]For[/COLOR] R = 1 To UBound(Ray(n))
Col = IIf(n >= 1, 2, 1)
MergedData(R, n + 1) = Ray(n)(R, Col)
[COLOR=Navy]Next[/COLOR] R
[COLOR=Navy]Next[/COLOR] n
Range("H1").Resize(rMax, 4).Value = MergedData
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]``````
Regards Mick

Where did the values for these arrays come from... ranges on a worksheet? If so were all the ranges on the same worksheet? If so, then I think we can form the MergedData array directly from the sheet, but we would need to know the address for each of the original ranges and which ones you used to create your xarray, yarray and zarray.

Rick

Thanks for taking the time in replying to the query. The arrays are not actually part of Excel. ie not ranges. the arrays are dealt with outside Excel. The "outside" program/macro (in vba) does a number of things (check & search for data into a 1/3 party application mainly) before writing to an excel worksheet a set of data (request by the user).

For the purpose of the query once can assume that the 3 arrays exist

Thanks

Regards

JXB

Rick

Thanks for taking the time in replying to the query. The arrays are not actually part of Excel. ie not ranges. the arrays are dealt with outside Excel. The "outside" program/macro (in vba) does a number of things (check & search for data into a 1/3 party application mainly) before writing to an excel worksheet a set of data (request by the user).

For the purpose of the query once can assume that the 3 arrays exist
How are the three existing arrays declared... fixed or dynamic? Also, what are they... Variant, String, Double, etc.?

How are the three existing arrays declared... fixed or dynamic? Also, what are they... Variant, String, Double, etc.?

Rick

For the purpose of the discussion, I am assuming that
1. the arrays are static and of same size
2. and the DIM is double (the arrays will always ever contains numbers)

Thanks

Regards

JXB

original thought seems to work as I get what I want

Sub test()

Dim xarray() As Double
Dim yarray() As Double
Dim zarray() As Double
Dim MergedData() As Double
Dim rMax As Integer

rMax = 4
ReDim xarray(1 To rMax, 1 To 2)
ReDim yarray(1 To rMax, 1 To 2)
ReDim zarray(1 To rMax, 1 To 2)
ReDim MergedData(1 To rMax, 1 To 4)

'Dummy arrays for testing
xarray(1, 1) = 0: xarray(2, 1) = 0.1: xarray(3, 1) = 0.2: xarray(4, 1) = 0.3
xarray(1, 2) = 0: xarray(2, 2) = 1: xarray(3, 2) = 2: xarray(4, 2) = 3

yarray(1, 1) = 0: yarray(2, 1) = 0.1: yarray(3, 1) = 0.2: yarray(4, 1) = 0.3
yarray(1, 2) = 0: yarray(2, 2) = 21: yarray(3, 2) = 22: yarray(4, 2) = 23

zarray(1, 1) = 0: zarray(2, 1) = 0.1: zarray(3, 1) = 0.2: zarray(4, 1) = 0.3
zarray(1, 2) = 0: zarray(2, 2) = 31: zarray(3, 2) = 32: zarray(4, 2) = 33

For i = 1 To rMax
MergedData(i, 1) = xarray(i, 1)
MergedData(i, 2) = xarray(i, 2)
MergedData(i, 3) = yarray(i, 2)
MergedData(i, 4) = zarray(i, 2)
Next

'"print" in excel to check
Range("A1").Resize(4, 4).Value = MergedData

End Sub

Replies
2
Views
844
Replies
35
Views
2K
Replies
3
Views
260
Replies
0
Views
393
Replies
7
Views
474

1,219,961
Messages
6,151,170
Members
451,012
Latest member
needvbahelp1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back