merging specific column of 3 array in a new array

[jxb]

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

 

[Rick Rothstein]

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.

 

[MickG]

Perhaps something like this:-

[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

 

[jxb]

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 Rothstein]

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.?

 

[jxb]

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

 

[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