merging specific column of 3 array in a new array

jxb

Board Regular
Joined
Apr 19, 2007
Messages
172
Office Version
  1. 2010
Platform
  1. Windows
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top