merging specific column of 3 array in a new array

jxb

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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,333
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
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
132
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
36,333
Office Version
  1. 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
132
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
132
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,274
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top