merging specific column of 3 array in a new array

jxb

Board Regular
Joined
Apr 19, 2007
Messages
134
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 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
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

jxb

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,591
Members
430,557
Latest member
MK15

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
Top