Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merge two arrays

Posted on 2001-09-13
18
Medium Priority
?
567 Views
Last Modified: 2012-06-27
I have 2 arrays, both with the same # of rows, but different # of fields.  I would like to take these tow arrays and merge them into a larger array.  (or just add one array to another)  Any Ideas?
0
Comment
Question by:scotiaceilidh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +3
18 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 6480136
You can create a dynamic array and enlarge it using ReDim.

Is that the type of array you mean?  Or are you talking about a recordset?
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6480145
Same # of rows but, diff # of records.

Do you mean you want to like...append to the right.  Not append to the bottom.

dill
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6480221
ReDim will only work with the new rows being IDENTICAL to the existing rows.

Since the two arrays appear to in fact be different, what do you think you need when you say you want to "merge" them?  How do you want to handle the items that are in one array, and NOT in the Other?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:rspahitz
ID: 6480321
Actually, you can redim the second dimension of a 2-dimensional array without concern.

So this works:

Dim SomeArray() as string
Redim SomeArray(10,30)
Redim Preserve SomeArray(10,50)

But this loses data:

Dim SomeArray() as string
Redim SomeArray(10,30)
Redim Preserve SomeArray(20,30)

...or so the MS documentation indicates.

Personally, I've successfully done the first one without loss of existing data.  However, when doing that, I had to change the understanding of the array dimensions.

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6480332
but the SECOND dimension is the NUMBER of ROWS (at least in most implementations)...what is gained if you still have the first dimension as 10?

you now have 10 instances of 50 items, rather than 10 instance of 30...what have you accomplished with that?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6480361
Well, first of all it depends on the requirements.  If the requirements are to add columns (but not rows) it works just fine.

If the requirements do not indicate how the data is to be stored, then you can simply transpose your array and call the first element columns and the second one rows.  This may require more work, so it has to be a seriously evaluated consideration.

Personally, given the apparent requirements, I'd probably go with either a collection or a recordset.

And actually, I think we're all confused about what the actual requirements are, so we can only guess at what solution would work best.
0
 
LVL 6

Expert Comment

by:holli
ID: 6480603
#!/usr/bin/perl
@a=(1,2,4); @b=(3,4,5); @all=(@a,@b);

couldn't resist.
holli
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6480630
Holli,
   Cute but irrelevant. read the original question:

"I have 2 arrays, both with the same # of rows, but different # of fields.  I would like to take these
tow arrays and merge them into a larger array.  (or just add one array to another)  Any Ideas? "

Your post appears to have the same number of FIELDS (3).  The original quest explicitly states SAME # or ROWS, diffrent FILEDS in those rows.  

The original question appears to be adding APPLES and ORANGES - does NOT make any sense, at least not to me.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6480637
the only thing that would come close to making any sense would be to APPEND the fields from ONE array ONTO the FIELDS from the other array, but only then IF (big IF) the rows were relevant to the same "THING" (essentially a One-To-One JOIN to two tables).  There was no indication that the data in a given row in one array was related to the data in the same row (or ANY row, for that matter) in the second array.
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6480707
holli - That was too funny.

Art - I have to agree with you.  We have no idea how the arrays look.

Is it like:
Array1:
ssn
firstname
lastname

Array2:
ssn
street
city
state
zip

And we have a 1 to 1 relationship with ssn and we want 1 array with ALL the fields.

Or is it like:
Array1:
ssn
firstname
lastname
phonenumber
street
city
state
zip

Array2
ssn
firstname
lastname
city
state
zip

And we have no relationship and want to append the 2nd array to the bottom of the 1st array and just leave the non-existant fields null?

Wouldn't it be pretty cool if scotiaceilidh would respond to clear up the questions.

dill
0
 
LVL 1

Author Comment

by:scotiaceilidh
ID: 6480923
The first Array is based on a record set, created from
'Place Record set into Array for Display
        aResults1 = rs.GetRows(, , 1).

The above array has only one field.

The next field is created from UDT

Public Type CustomArray
 Value1 As String
 Value2 As String
 Value3 As String
 Value4 As String
End Type

and populated by

aResults2(i).Value1 = sJob
        aResults2(i).Value2 = Status(lngRetVal)
        aResults2(i).Value3 = Format(mstrLastRunDate, "dddd, mmm d yyyy")
        aResults2(i).Value4 = mstrLastRunStatus

I am currently trying to place aResults1 into Value1 of aResults2.  I am getting a subscript error.  Here is all the code.

'Place Record set into Array for Display
        aResults1 = rs.GetRows(, , 1)
       
       
'Close Database
Call objConn.CloseRecordset

Set rsJob = objConn.GetRecordset(sSQL)
       
'=======================================================================================
'HERE IS THE BUILDING OF THE SECOND ARRAY WHICH IS THE 2ND HALF OF DISPLAYED DATA
'=======================================================================================
'Loop thru the Job Names
iRows = UBound(aResults1, 2)
iCols = UBound(aResults1, 1)
'Stop at the end
iStop = iRows

'Set increment for Array
i = 0
For iRowLoop = iStart To iStop - 1
    For iColLoop = 0 To iCols
            sJob = aResults1(iColLoop, iRowLoop)
            'Now that we have the Job Name, we call a function to return the "Job Status"
            '"Date Last Run", and the "Last Run Outcome".
            Dim lngRetVal As Integer
           
       
'create the SQL DMO wrapper object
Set objGRC_SQLDMO = CreateObject("GRC_SQLDMO.clsGRC_SQLDMO")
               
            varJobName = rsJob!strJobName
           'lngRetVal holds the status returned
           lngRetVal = objGRC_SQLDMO.SQLIsJobRunning(strServer:=varServer, _
                                    strUser:="StartJob", _
                                    strPassword:="dnjobber", _
                                    strJobName:=varJobName, _
                                    strLastRunDate:=mstrLastRunDate, _
                                    strLastRunStatus:=mstrLastRunStatus)
   
        'Place the values into the 2nd array
        aResults2(i).Value1 = sJob
        aResults2(i).Value2 = Status(lngRetVal)
        aResults2(i).Value3 = Format(mstrLastRunDate, "dddd, mmm d yyyy")
        aResults2(i).Value4 = mstrLastRunStatus

'Increment counter to add next row to the array.

i = i + 1
         
                 
    Next
   
Next

'========================================================================================

'start building the table
sHTML = "<Table Width = 550 align=left border=0 CELLSPACING=3 CELLPADDING=1 bgcolor =Silver>"

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Loop thru the Job Names
iRows = UBound(aResults2, 2)
iCols = UBound(aResults2, 1)
'Stop at the end
iStop = iRows
   

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


    For iRowLoop = iStart To iStop
        sHTML = sHTML & "<TR>"
       
        For iColLoop = 0 To iCols
            sHTML = sHTML & "<TD width=100>"
            sHTML = sHTML & aResults1(iColLoop, iRowLoop) & " "
            sHTML = sHTML & "</TD>"
            '===================================================
            'Merge the 2nd Array with the first.
            '===================================================
            'sHTML = sHTML & "<TD width=100>"
            'sHTML = sHTML & aResults2(iColLoop, iRowLoop) & " "
            'sHTML = sHTML & "</TD>"
            '===================================================
        Next
        sHTML = sHTML & "</TR>"
    Next

sHTML = sHTML & "</table>"

'Write to Web Page
OrderByTable = sHTML


0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6480951
scotiaceilidh, is this not working that you want to find a way to merge the arrays?

I'm thinking that if you really want to do as you say, you can try something like this:

Dim BigArray() as string
...

ReDim BigArray(iCols, iRows)

Then start transferring the data from the two arrays into BigArray and use as necessary.
0
 
LVL 1

Author Comment

by:scotiaceilidh
ID: 6480980
rspahitz,
Yes, Thank you.  But I do not know how to transfer the data from the two arrays into a larger one.  I will start with this and the Redim, but suspect I will be stuck on inserting the data into the Big array.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6481007
Gee, it looks like you already have the code...

...

iRows1 = UBound(aResults1, 2)
iCols1 = UBound(aResults1, 1)
iRows2 = UBound(aResults1, 2)
iCols2 = UBound(aResults1, 1)

ReDim BigArray(iCols1 + iCols2, iRows1 + iRows2)

For iRowLoop = 0 to iRows1
  For iColLoop = 0 To iCols1
     BigArray(iColLoop, iRowLoop) = aResults1(iColLoop, iRowLoop)
  Next iColLoop
Next iRowLoop

For iRowLoop = 0 to iRows2
  For iColLoop = 0 To iCols2
     BigArray(iColLoop + iCols1 + 1, iRowLoop +  iRows1 + 1) = aResults1(iColLoop, iRowLoop)
  Next iColLoop
Next iRowLoop

...
I haven't tested this, no it may not be perfect, but should be close enough that you can figure it out.
0
 
LVL 1

Author Comment

by:scotiaceilidh
ID: 6481049
BigArray(iColLoop + iCols1 + 1, iRowLoop +  iRows1 + 1) = aResults1(iColLoop, iRowLoop)


Here is where I get a sub script error.  I think this is due to this array being a UDT and not one created from a recordset.  My teleprompting indicates that there is just one column in the 2nd array, although I mean there to be several.  Any Ideas.  
0
 
LVL 9

Expert Comment

by:miron
ID: 6481066
You might get it all clearer codewise by creating a class module and in that class module put both data objects, that constitute array dimentions.
then declare an array of this class type objects;
next, create one dimentional array of class objects and assign value to class members via dot operator.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 160 total points
ID: 6482941
scotiaceilidh, I think I forgot to add one in the redim:

ReDim BigArray(iCols1 + iCols2 + 1, iRows1 + iRows2 + 1)

If the same problem recurs, tell us the values of the variables iColLoop, iCols1, iRowLoop, iRows1 when it gives you the error.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6488162
Glad to be of help, scotiaceilidh.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question