Append One Array to Another, and Consolidate
 

I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.

Append One Array to Another, and Consolidate
 

Stratuser wrote:
I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.

I'd do it this way:

go through the smaller array, check for each element if the values
duplicate - if not - redim the bigg array by one, and add the just
processed element.

Does that make sense?

Append One Array to Another, and Consolidate
 

As the redimming process is the expensive step, it would be more efficient
to Redim Preserve to include all of the small array, process as Morris
described, then redim back down to the number of actual used elements
This assumes by "comsolidate" you mean "discard" .

NickHK

"Morris"
groups.com...

Stratuser wrote:
I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays
onto
a blank worksheet, sorting the data there, and then doing a search loop
that
consolidates the duplicate elements. I'm thinking there might be a way
to do
it entirely within arrays, off the worksheet.

I'd do it this way:

go through the smaller array, check for each element if the values
duplicate - if not - redim the bigg array by one, and add the just
processed element.

Does that make sense?

Append One Array to Another, and Consolidate
 

Yes, but there is one other thing: My arrays are two-dimensional, based on a
row and column structure, like a range. If I use ReDim Preserve to increase
the big array to add the non-duplicate elements, I can only increase the last
dimension of the array. Since I'm adding rows in effect, I guess I would
need the array structure to be Array(column, row) instead of Array(row,
column). Right?

"Morris" wrote:


Stratuser wrote:
I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.

I'd do it this way:

go through the smaller array, check for each element if the values
duplicate - if not - redim the bigg array by one, and add the just
processed element.

Does that make sense?

Append One Array to Another, and Consolidate
 

Stratuser wrote:
Yes, but there is one other thing: My arrays are two-dimensional, based on a
row and column structure, like a range. If I use ReDim Preserve to increase
the big array to add the non-duplicate elements, I can only increase the last
dimension of the array. Since I'm adding rows in effect, I guess I would
need the array structure to be Array(column, row) instead of Array(row,
column). Right?

dunno :)

What kind of values have you got in the inner array? could you
consolidate them to a string using Join method - for the time of
comparation, and array redimensioning, and then do a loop throughout
the array to split them back? It's just a loose idea, as I'd prefere to
see the example itself

Append One Array to Another, and Consolidate
 

Yes, using the Preserve keyword, which you obviously need, you can only
resize the last dimension (forgot to mention that).
You can use "Application.WorksheetFunction.Transpose()", or create you
intial arrays "the other way around".

NickHK

"Stratuser" ...
Yes, but there is one other thing: My arrays are two-dimensional, based
on a
row and column structure, like a range. If I use ReDim Preserve to
increase
the big array to add the non-duplicate elements, I can only increase the
last
dimension of the array. Since I'm adding rows in effect, I guess I would
need the array structure to be Array(column, row) instead of Array(row,
column). Right?

"Morris" wrote:


Stratuser wrote:
I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays
onto
a blank worksheet, sorting the data there, and then doing a search loop
that
consolidates the duplicate elements. I'm thinking there might be a way
to do
it entirely within arrays, off the worksheet.

I'd do it this way:

go through the smaller array, check for each element if the values
duplicate - if not - redim the bigg array by one, and add the just
processed element.

Does that make sense?

Append One Array to Another, and Consolidate
 

The values are both strings (ticker symbols) and numbers associated with the
tickers. They are pulled from columns on a worksheet.

"Morris" wrote:


Stratuser wrote:
Yes, but there is one other thing: My arrays are two-dimensional, based on a
row and column structure, like a range. If I use ReDim Preserve to increase
the big array to add the non-duplicate elements, I can only increase the last
dimension of the array. Since I'm adding rows in effect, I guess I would
need the array structure to be Array(column, row) instead of Array(row,
column). Right?

dunno :)

What kind of values have you got in the inner array? could you
consolidate them to a string using Join method - for the time of
comparation, and array redimensioning, and then do a loop throughout
the array to split them back? It's just a loose idea, as I'd prefere to
see the example itself

Append One Array to Another, and Consolidate
 

Could you start off with all the data in a single array ?
Then you do not have to combine them later.

Dim var As Variant

var = Union(Range("A1:B4"), Range("A5:B8"))

NickHK

"Stratuser" ...
The values are both strings (ticker symbols) and numbers associated with
the
tickers. They are pulled from columns on a worksheet.

"Morris" wrote:


Stratuser wrote:
Yes, but there is one other thing: My arrays are two-dimensional,
based on a
row and column structure, like a range. If I use ReDim Preserve to
increase
the big array to add the non-duplicate elements, I can only increase
the last
dimension of the array. Since I'm adding rows in effect, I guess I
would
need the array structure to be Array(column, row) instead of Array(row,
column). Right?

dunno :)

What kind of values have you got in the inner array? could you
consolidate them to a string using Join method - for the time of
comparation, and array redimensioning, and then do a loop throughout
the array to split them back? It's just a loose idea, as I'd prefere to
see the example itself

Append One Array to Another, and Consolidate
 

Unfortunately, I can't start off with them in a single array, because it
takes one complex operation to get the data for the first array, and then
another operation to get the data for the second array. I was trying to get
all the data into one array to keep from having to do endless lookups between
the two arrays later in the process.

I was hoping to combine the arrays, but it looks like maybe I should look
into whether I can just do the work with two separate arrays -- or go back to
doing things on a worksheet, which is my old method (it does work, but it
isn't as clever as using arrays).

"NickHK" wrote:

Could you start off with all the data in a single array ?
Then you do not have to combine them later.

Dim var As Variant

var = Union(Range("A1:B4"), Range("A5:B8"))

NickHK

"Stratuser" ...
The values are both strings (ticker symbols) and numbers associated with
the
tickers. They are pulled from columns on a worksheet.

"Morris" wrote:


Stratuser wrote:
Yes, but there is one other thing: My arrays are two-dimensional,
based on a
row and column structure, like a range. If I use ReDim Preserve to
increase
the big array to add the non-duplicate elements, I can only increase
the last
dimension of the array. Since I'm adding rows in effect, I guess I
would
need the array structure to be Array(column, row) instead of Array(row,
column). Right?

dunno :)

What kind of values have you got in the inner array? could you
consolidate them to a string using Join method - for the time of
comparation, and array redimensioning, and then do a loop throughout
the array to split them back? It's just a loose idea, as I'd prefere to
see the example itself

Append One Array to Another, and Consolidate
 

Stratuser wrote:
I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.
What do you mean by "consolidate duplicate elements"? E.g., assume your
arrays were 2x2 and 3x2 and the combined elements before "consolidation"
were

1 2
3 4
5 3
7 8
9 10

What would the array be after "consolidation"?

Alan Beban

Append One Array to Another, and Consolidate
 

The duplicate elements I was referring to would occur in the first column.
If there were two elements in the first column with the same value (ticker
symbols in my case) as a result of appending the two arrays, I would
substitute any positive values in the columns to the right for blank or zero
values for the same ticker symbol. To illustrate, here's an example of two
arrays, 2x5 and 3x5:

array1:

A Tech 0 5 9
B Energy 0 6 4

array 2:

A Tech 4 0 9
K Materials 6 0 8
L Utilities 7 0 3

The two arrays would consolidate like so, because ticker A is in both arrays:

A Tech 4 5 9
B Energy 0 6 4
K Materials 6 0 8
L Utilities 7 0 3



"Alan Beban" wrote:

Stratuser wrote:
I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.
What do you mean by "consolidate duplicate elements"? E.g., assume your
arrays were 2x2 and 3x2 and the combined elements before "consolidation"
were

1 2
3 4
5 3
7 8
9 10

What would the array be after "consolidation"?

Alan Beban

Append One Array to Another, and Consolidate
 

Stratuser wrote:
The duplicate elements I was referring to would occur in the first column.
If there were two elements in the first column with the same value (ticker
symbols in my case) as a result of appending the two arrays, I would
substitute any positive values in the columns to the right for blank or zero
values for the same ticker symbol. To illustrate, here's an example of two
arrays, 2x5 and 3x5:

array1:

A Tech 0 5 9
B Energy 0 6 4

array 2:

A Tech 4 0 9
K Materials 6 0 8
L Utilities 7 0 3

The two arrays would consolidate like so, because ticker A is in both arrays:

A Tech 4 5 9
B Energy 0 6 4
K Materials 6 0 8
L Utilities 7 0 3

I'm still not quite getting the algorithm. Suppose everything were as
above except that the first row of array1 were

A Tech 3 5 7

what would be the value for resultingArray(1,3) and resultingArray(1,5)?

Alan Beban

Append One Array to Another, and Consolidate
 

The third column of the first array will be empty in every case, and the
fourth column of the second array will be empty in every case, so the example
you have won't occur.

"Alan Beban" wrote:

Stratuser wrote:
The duplicate elements I was referring to would occur in the first column.
If there were two elements in the first column with the same value (ticker
symbols in my case) as a result of appending the two arrays, I would
substitute any positive values in the columns to the right for blank or zero
values for the same ticker symbol. To illustrate, here's an example of two
arrays, 2x5 and 3x5:

array1:

A Tech 0 5 9
B Energy 0 6 4

array 2:

A Tech 4 0 9
K Materials 6 0 8
L Utilities 7 0 3

The two arrays would consolidate like so, because ticker A is in both arrays:

A Tech 4 5 9
B Energy 0 6 4
K Materials 6 0 8
L Utilities 7 0 3

I'm still not quite getting the algorithm. Suppose everything were as
above except that the first row of array1 were

A Tech 3 5 7

what would be the value for resultingArray(1,3) and resultingArray(1,5)?

Alan Beban

Append One Array to Another, and Consolidate
 

Well, I guess that gets us closer, but not quite there. Suppose then
that the first row of array1 were

A Tech 0 5 7

or the first row of array2 were

A Tech 4 0 7

What is the value of resultingArray(1,5)? 7? 9? Something else? How is
it determined?

Alan

Stratuser wrote:
The third column of the first array will be empty in every case, and the
fourth column of the second array will be empty in every case, so the example
you have won't occur.

"Alan Beban" wrote:

Stratuser wrote:
The duplicate elements I was referring to would occur in the first column.
If there were two elements in the first column with the same value (ticker
symbols in my case) as a result of appending the two arrays, I would
substitute any positive values in the columns to the right for blank or zero
values for the same ticker symbol. To illustrate, here's an example of two
arrays, 2x5 and 3x5:

array1:

A Tech 0 5 9
B Energy 0 6 4

array 2:

A Tech 4 0 9
K Materials 6 0 8
L Utilities 7 0 3

The two arrays would consolidate like so, because ticker A is in both arrays:

A Tech 4 5 9
B Energy 0 6 4
K Materials 6 0 8
L Utilities 7 0 3

I'm still not quite getting the algorithm. Suppose everything were as
above except that the first row of array1 were

A Tech 3 5 7

what would be the value for resultingArray(1,3) and resultingArray(1,5)?

Alan Beban

Append One Array to Another, and Consolidate
 

Hi Stratuser,

not quite clear what the result should be,
if it would be a two dimensional array(400, 5)
then there would be empty entries.

Writing not empty entries into still another array,
would not be a problem, but it would have to be
a 1-dimensional array.

With previous sorting things would be much easier,
My sample preserves the original order,
otherwise it wouldn't have been a challenge. ;-)

Sub test00001()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim s As String
Dim x As Long
Dim f As Boolean ' found
Dim array1(1 To 100, 1 To 5) As String
Dim array2(1 To 300, 1 To 5) As String
Dim array3(1 To 400, 1 To 5) As String
Dim array4(1 To 400, 1 To 5) As String
Dim array5() As String ' not used yet
Randomize
' fill array 1 with random numbers as strings
For a = 1 To 100
For b = 1 To 5
x = Int(100 * Rnd + 100)
s = Format(x, "000")
array1(a, b) = s
Next
Next
' fill array 2 with random numbers as strings
For a = 1 To 300
For b = 1 To 5
x = Int(999 * Rnd + 1)
s = Format(x, "000")
array2(a, b) = s
Next
Next
' combine arra1 and array2 into array 3
For a = 1 To 100
For b = 1 To 5
array3(a, b) = array1(a, b)
Next
Next
For a = 1 To 300
For b = 1 To 5
array3(a + 100, b) = array2(a, b)
Next
Next
' put strings in array 4
' that aren't found in array 4 before
' compared to array 3
For a = 1 To 400
f = False
For b = 1 To 5
For c = 1 To 400
For d = 1 To 5
If array3(a, b) = array4(c, d) Then
f = True ' found
End If
Next
Next
If f = False Then
array4(a, b) = array3(a, b)
f = True
End If
Next
Next

' output in word for my convenience
' in an empty document
' ctrl a, table sort, for checking
' from here on it's plain sailing
' to count all not empty entries
' redim still another array (array5)
' and fill it

For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
selection.TypeText array4(a, b) & vbCr
End If
Next
Next
' ctrl a, table sort, for checking

End Sub

There are numerous ways to achieve the same goal.
The above was coded to show a possible way.
In my way of coding there comes first a solution,
as wierd as it may be, just to proof whether
it is doable at all, and then comes optimization.


Note that this is a word-macro,
but the only difference from Excel is the line
for checking the output:

selection.TypeText array4(a, b) & vbCr

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Append One Array to Another, and Consolidate
 

:-(

x = Int(100 * Rnd + 100)

should be

x = Int(999 * Rnd + 1)

in the first loop, too.

But it is in no way relevant.

Helmut Weber

Append One Array to Another, and Consolidate
 

Getting closer...

Sub test00001x()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim s As String
Dim x As Long
Dim f As Boolean ' found
Dim array1(1 To 100, 1 To 5) As String
Dim array2(1 To 300, 1 To 5) As String
Dim array3(1 To 400, 1 To 5) As String
Dim array4(1 To 400, 1 To 5) As String
Dim array5() As String
Randomize
' fill array 1 with random numbers as strings
For a = 1 To 100
For b = 1 To 5
x = Int(100 * Rnd + 100)
s = Format(x, "000")
array1(a, b) = s
Next
Next
' fill array 2 with random numbers as strings
For a = 1 To 300
For b = 1 To 5
x = Int(999 * Rnd + 1)
s = Format(x, "000")
array2(a, b) = s
Next
Next
' combine arra1 and array2 into array 3
For a = 1 To 100
For b = 1 To 5
array3(a, b) = array1(a, b)
Next
Next
For a = 1 To 300
For b = 1 To 5
array3(a + 100, b) = array2(a, b)
Next
Next
' put strings in array 4
' that aren't found in array 4 before
' compared to array 3
For a = 1 To 400
For b = 1 To 5
f = False
For c = 1 To 400
For d = 1 To 5
If array3(a, b) = array4(c, d) Then
f = True ' found
End If
Next
Next
If f = False Then
array4(a, b) = array3(a, b)
f = True
End If
Next
Next

' output in word for my convenience
' in an empty document
' ctrl a, table sort, for checking
' from here on it's plain sailing
' to count all not empty entries
' redim still another array
' and fill it
c = 0
For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
c = c + 1
End If
Next
Next
ReDim array5(c)
c = 0
For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
c = c + 1
array5(c) = array4(a, b)
selection.TypeText array5(c) & vbCr
' or filling excel cells
' not a problem I suppose
End If
Next
Next
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Append One Array to Another, and Consolidate
 

Getting closer
and still faster...

Sub test00001y()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim s As String
Dim x As Long
Dim f As Boolean ' found
Dim array1(1 To 100, 1 To 5) As String
Dim array2(1 To 300, 1 To 5) As String
Dim array3(1 To 400, 1 To 5) As String
Dim array4(1 To 400, 1 To 5) As String
Dim array5() As String
Randomize
' fill array 1 with random numbers as strings
For a = 1 To 100
For b = 1 To 5
x = Int(100 * Rnd + 100)
s = Format(x, "000")
array1(a, b) = s
Next
Next
' fill array 2 with random numbers as strings
For a = 1 To 300
For b = 1 To 5
x = Int(999 * Rnd + 1)
s = Format(x, "000")
array2(a, b) = s
Next
Next
' combine arra1 and array2 into array 3
For a = 1 To 100
For b = 1 To 5
array3(a, b) = array1(a, b)
Next
Next
For a = 1 To 300
For b = 1 To 5
array3(a + 100, b) = array2(a, b)
Next
Next
' put strings in array 4
' that aren't found in array 4 before
' compared to array 3
For a = 1 To 400
For b = 1 To 5
f = False
For c = 1 To 400
For d = 1 To 5
If array3(a, b) = array4(c, d) Then
f = True ' found
Exit For
End If
Next
If f = True Then Exit For
Next
If f = False Then
array4(a, b) = array3(a, b)
f = True
End If
Next
Next

' output in word for my convenience
' in an empty document
' ctrl a, table sort, for checking
' from here on it's plain sailing
' to count all not empty entries
' redim still another array
' and fill it
c = 0
For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
c = c + 1
End If
Next
Next
ReDim array5(c)
c = 0
For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
c = c + 1
array5(c) = array4(a, b)
selection.TypeText array5(c) & vbCr
' or filling excel cells
' not a problem I suppose
End If
Next
Next
End Sub

Helmut Weber

Append One Array to Another, and Consolidate
 

The fifth element is the monthly return of the stock with ticker "A", which
is the same in both cases, so it would still be 7 in the consolidated array.

"Alan Beban" wrote:

Well, I guess that gets us closer, but not quite there. Suppose then
that the first row of array1 were

A Tech 0 5 7

or the first row of array2 were

A Tech 4 0 7

What is the value of resultingArray(1,5)? 7? 9? Something else? How is
it determined?

Alan

Stratuser wrote:
The third column of the first array will be empty in every case, and the
fourth column of the second array will be empty in every case, so the example
you have won't occur.

"Alan Beban" wrote:

Stratuser wrote:
The duplicate elements I was referring to would occur in the first column.
If there were two elements in the first column with the same value (ticker
symbols in my case) as a result of appending the two arrays, I would
substitute any positive values in the columns to the right for blank or zero
values for the same ticker symbol. To illustrate, here's an example of two
arrays, 2x5 and 3x5:

array1:

A Tech 0 5 9
B Energy 0 6 4

array 2:

A Tech 4 0 9
K Materials 6 0 8
L Utilities 7 0 3

The two arrays would consolidate like so, because ticker A is in both arrays:

A Tech 4 5 9
B Energy 0 6 4
K Materials 6 0 8
L Utilities 7 0 3

I'm still not quite getting the algorithm. Suppose everything were as
above except that the first row of array1 were

A Tech 3 5 7

what would be the value for resultingArray(1,3) and resultingArray(1,5)?

Alan Beban

Append One Array to Another, and Consolidate
 

Thanks, it's helpful to see how other people would do this.

"Helmut Weber" wrote:

Getting closer
and still faster...

Sub test00001y()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim s As String
Dim x As Long
Dim f As Boolean ' found
Dim array1(1 To 100, 1 To 5) As String
Dim array2(1 To 300, 1 To 5) As String
Dim array3(1 To 400, 1 To 5) As String
Dim array4(1 To 400, 1 To 5) As String
Dim array5() As String
Randomize
' fill array 1 with random numbers as strings
For a = 1 To 100
For b = 1 To 5
x = Int(100 * Rnd + 100)
s = Format(x, "000")
array1(a, b) = s
Next
Next
' fill array 2 with random numbers as strings
For a = 1 To 300
For b = 1 To 5
x = Int(999 * Rnd + 1)
s = Format(x, "000")
array2(a, b) = s
Next
Next
' combine arra1 and array2 into array 3
For a = 1 To 100
For b = 1 To 5
array3(a, b) = array1(a, b)
Next
Next
For a = 1 To 300
For b = 1 To 5
array3(a + 100, b) = array2(a, b)
Next
Next
' put strings in array 4
' that aren't found in array 4 before
' compared to array 3
For a = 1 To 400
For b = 1 To 5
f = False
For c = 1 To 400
For d = 1 To 5
If array3(a, b) = array4(c, d) Then
f = True ' found
Exit For
End If
Next
If f = True Then Exit For
Next
If f = False Then
array4(a, b) = array3(a, b)
f = True
End If
Next
Next

' output in word for my convenience
' in an empty document
' ctrl a, table sort, for checking
' from here on it's plain sailing
' to count all not empty entries
' redim still another array
' and fill it
c = 0
For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
c = c + 1
End If
Next
Next
ReDim array5(c)
c = 0
For a = 1 To 400
For b = 1 To 5
If array4(a, b) < "" Then
c = c + 1
array5(c) = array4(a, b)
selection.TypeText array5(c) & vbCr
' or filling excel cells
' not a problem I suppose
End If
Next
Next
End Sub

Helmut Weber

Append One Array to Another, and Consolidate
 

Stratuser wrote:
The fifth element is the monthly return of the stock with ticker "A", which
is the same in both cases, so it would still be 7 in the consolidated array.

I'm afraid I still don't see eactly what the "duplicate consolidation"
specs are, but if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the two
arrays can be combined in a new myArray1 with

i = UBound(myArray1)
j = UBound(myArray2)
k = UBound(myArray1, 2)
ResizeArray myArray1, i + j, k
ReplaceSubArray myArray1, myArray2, i + 1, 1

Alan Beban

Append One Array to Another, and Consolidate
 

Thanks, these array functions are very interesting, and I'll look into them.

"Alan Beban" wrote:

Stratuser wrote:
The fifth element is the monthly return of the stock with ticker "A", which
is the same in both cases, so it would still be 7 in the consolidated array.

I'm afraid I still don't see eactly what the "duplicate consolidation"
specs are, but if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the two
arrays can be combined in a new myArray1 with

i = UBound(myArray1)
j = UBound(myArray2)
k = UBound(myArray1, 2)
ResizeArray myArray1, i + j, k
ReplaceSubArray myArray1, myArray2, i + 1, 1

Alan Beban

Append One Array to Another, and Consolidate
 

If your still interested, I have developed a good algorithm to do exactly
what you want.
Regards,
Albert C.

"Stratuser" wrote:

I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.

Append One Array to Another, and Consolidate
 

What do you mean by "consolidates the duplicate elements"?

Alan Beban

Albert wrote:
If your still interested, I have developed a good algorithm to do exactly
what you want.
Regards,
Albert C.

"Stratuser" wrote:

I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.

Append One Array to Another, and Consolidate
 

It means that duplicate registers will be deleted from the array.

"Alan Beban" wrote:

What do you mean by "consolidates the duplicate elements"?

Alan Beban

Albert wrote:
If your still interested, I have developed a good algorithm to do exactly
what you want.
Regards,
Albert C.

"Stratuser" wrote:

I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the arrays onto
a blank worksheet, sorting the data there, and then doing a search loop that
consolidates the duplicate elements. I'm thinking there might be a way to do
it entirely within arrays, off the worksheet.

Append One Array to Another, and Consolidate
 

I'm interested. Wonder if you can show your coding here.

"Albert" wrote in message
...
It means that duplicate registers will be deleted from the array.

"Alan Beban" wrote:

What do you mean by "consolidates the duplicate elements"?

Alan Beban

Albert wrote:
If your still interested, I have developed a good algorithm to do
exactly
what you want.
Regards,
Albert C.

"Stratuser" wrote:

I have two arrays, both of which are two-dimensional (for example,
Array1(100,5) and Array2(300,5), of the same type (Variant). I want
to
combine them into a single array, and then I want to consolidate any
duplicate elements. Any ideas on the best way to do this?

In the past, I've handled this by just dumping the data from the
arrays onto
a blank worksheet, sorting the data there, and then doing a search
loop that
consolidates the duplicate elements. I'm thinking there might be a
way to do
it entirely within arrays, off the worksheet.

Append One Array to Another, and Consolidate
 

Here goes. It uses a sort algorithm I got previously called QuickSort. I will
post it next.
Be careful with the comments I made. The initial apostrophes always get lost
in the copy paste procedure.
Regards
Albert C.

Public ConsolidatedArray() As Variant
Sub CallArrayConsolidator()

Dim ArrayTest1() As Variant
Dim ArrayTest2() As Variant

ArrayTest1 = Range("A1", "L35")
ArrayTest2 = Range("A20", "M42")
Call ArrayConsolidator(ArrayTest1, ArrayTest2)
Range("A50", "L100") = ConsolidatedArray

End Sub
Sub ArrayConsolidator(Array1, Array2)

' Alberto Cattan Rozenfarb
'
' 17 de Noviembre de 2006

' Array1 y Array2 son dos arrays exógenos que pueden o no tener elementos
duplicados
' Array1 and Array2 are two exogenous arrays which may or may not have
duplicated registers
' Array1 = Range("A1", "L19")
' Array2 = Range("A20", "L42")
If Not UBound(Array1, 2) = UBound(Array2, 2) Then
MsgBox "La segunda dimensión de los dos arrays debe tener el mismo
UBound."
Exit Sub
End If

' Se crea Array3 que es una combinación de Array1 y Array2.
' Tiene 2 columnas extra para reconocer registros duplicados.
' We create Array3 which is a blunt combination of Array1 and Array2
' It has 2 extra columns which we will use to identify and eliminate
duplicates.
ReDim Array3(1 To UBound(Array1) + UBound(Array2), 1 To UBound(Array1,
2) + 2)
For x = 1 To UBound(Array1)
For Y = 1 To UBound(Array1, 2)
Array3(x, Y) = Array1(x, Y)
Next Y
Next x
For x = 1 To UBound(Array2)
For Y = 1 To UBound(Array1, 2)
Array3(UBound(Array1) + x, Y) = Array2(x, Y)
Next Y
Next x

' Se genera una columna con un Concatenado de todas las demás columnas.
' Esta es la llave para reconocer duplicados
' We generate a column which concatenates all the columns in arrays 1&2
' We will use this column to identify duplicates
For x = 1 To UBound(Array3)
For g = 1 To UBound(Array1, 2)
Array3(x, UBound(Array2, 2) + 1) = Array3(x, UBound(Array2, 2) +
1) & Array3(x, g)
Next g
Next x

' Se ordena la matriz por esta (pen-última) columna
' We sort the array with the concatenated column as key
Call QuickSort(Array3, UBound(Array3, 2) - 1, LBound(Array3),
UBound(Array3), True)

' En la última columna se marcan los registros repetidos
' In the last column we identify and mark duplicates
For x = 2 To UBound(Array3)
If Array3(x, UBound(Array3, 2) - 1) = Array3(x - 1, UBound(Array3,
2) - 1) Then
Array3(x, UBound(Array3, 2)) = "REPETIDO"
End If
Next x

' Se ordena la matriz por orden de registros repetidos y no repetidos.
' We sort the array with the DuplicateIdentify column as key
Call QuickSort(Array3, UBound(Array3, 2), LBound(Array3),
UBound(Array3), True)

' Se cuenta la cantidad de registros NO repetidos, que va a ser el ubound
de la matriz limpia de duplicados.
' We determine the amount of non-duplicates in order to ReDim the clean
array
x = 1
Do Until Array3(x, UBound(Array3, 2)) < Empty Or x = UBound(Array3, 1)
x = x + 1
Loop

' Array4 es la matriz limpia de duplicados. En vista de que los duplicados
ya fueron excluÃ*dos, ya no se necesitan las dos columnas extras.
' Array4 is the duplicate-free array. Since the duplicates have been
identified and sent to the end, we no longer need the extra columns.
ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x

End Sub

Append One Array to Another, and Consolidate
 

Sub QuickSort(SortArray, col, L, R, bAscending)

'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'Modified to sort on a specified column in a 2D array
'Modified to do Ascending or Descending
Dim i, j, x, Y, mm

i = L
j = R
x = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < x And i < R)
i = i + 1
Wend
While (x < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) x And i < R)
i = i + 1
Wend
While (x SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)

End Sub

Append One Array to Another, and Consolidate
 

Ooops, made a little mistake in CallArrayConsolidator
sorry. Here it is corrected.

Sub CallArrayConsolidator()

Dim ArrayTest1() As Variant
Dim ArrayTest2() As Variant

ArrayTest1 = Range("A1", "L35")
ArrayTest2 = Range("A20", "L42")
Call ArrayConsolidator(ArrayTest1, ArrayTest2)
Range("A50", "L100") = ConsolidatedArray

End Sub

Append One Array to Another, and Consolidate
 

Thanks a lot, my friend.
Best regards!

Phil

"Albert" wrote in message
...
Ooops, made a little mistake in CallArrayConsolidator
sorry. Here it is corrected.

Sub CallArrayConsolidator()

Dim ArrayTest1() As Variant
Dim ArrayTest2() As Variant

ArrayTest1 = Range("A1", "L35")
ArrayTest2 = Range("A20", "L42")
Call ArrayConsolidator(ArrayTest1, ArrayTest2)
Range("A50", "L100") = ConsolidatedArray

End Sub

Append One Array to Another, and Consolidate
 

You are welcome.
Your feedback will be greatly appreciated so I can improve the algorithm.
Albert

Append One Array to Another, and Consolidate
 

Albert wrote:
You are welcome.
Your feedback will be greatly appreciated so I can improve the algorithm.
Albert
I ran the programs with ranges A1:D3 and A5:D7.

The first had
1 2 3 4
5 1 7 3
9 10 11 12

The second had
21 9 21 24
25 26 10 28
29 30 31 11

The resulting consolidated array was
29 30 31 11
5 1 7 3
9 10 11 12
1 2 3 4
21 9 23 24

As you can see, it ignored the 2nd row of the second array, and it did
not eliminate the duplicates.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to one's workbook one can get

1 2 3 4
5 7 9 10
11 12 21 23
24 25 26 28
29 30 31 0

with the following code (variables are not declared)

Sub abtest1()
arr1 = Range("A1:D3")
arr2 = Range("A5:D7")
iCols = UBound(arr1, 2)
arrU = ArrayUniques(MakeArray(arr1, arr2, 1))
k = ArrayCount(arrU)
If k / iCols = Int(k / iCols) Then
q = (k / iCols)
Else
q = Int(k / iCols) + 1
End If
ConsolidatedArray = ArrayReshape(arrU, q, iCols)
Range("A11").Resize(q, iCols).Value = ConsolidatedArray
End Sub

Alan Beban

Append One Array to Another, and Consolidate
 

You are right, my algorithm was flawed.
Here it is fixed.
Please tell me how it you find any more bugs.
Thx,
Albert

Sub ArrayConsolidator(Array1, Array2)

' Alberto Cattan Rozenfarb
'
' 17 de Noviembre de 2006

' Array1 y Array2 son dos arrays exógenos que pueden o no tener elementos
duplicados
' Array1 and Array2 are two exogenous arrays which may or may not have
duplicated registers
' Array1 = Range("A1", "L19")
' Array2 = Range("A20", "L42")
If Not UBound(Array1, 2) = UBound(Array2, 2) Then
MsgBox "La segunda dimensión de los dos arrays debe tener el mismo
UBound." & vbCrLf & "The second dimension for both arrays must have the same
UBound."
Exit Sub
End If

' Se crea Array3 que es una combinación de Array1 y Array2.
' Tiene 2 columnas extra para reconocer registros duplicados.
' We create Array3 which is a blunt combination of Array1 and Array2
' It has 2 extra columns which we will use to identify and eliminate
duplicates.
ReDim Array3(1 To UBound(Array1) + UBound(Array2), 1 To UBound(Array1,
2) + 2)
For x = 1 To UBound(Array1)
For Y = 1 To UBound(Array1, 2)
Array3(x, Y) = Array1(x, Y)
Next Y
Next x
For x = 1 To UBound(Array2)
For Y = 1 To UBound(Array1, 2)
Array3(UBound(Array1) + x, Y) = Array2(x, Y)
Next Y
Next x

' Se genera una columna con un Concatenado de todas las demás columnas.
' Esta es la llave para reconocer duplicados
' We generate a column which concatenates all the columns in arrays 1&2
' We will use this column to identify duplicates
For x = 1 To UBound(Array3)
For g = 1 To UBound(Array1, 2)
Array3(x, UBound(Array2, 2) + 1) = Array3(x, UBound(Array2, 2) +
1) & Array3(x, g)
Next g
Next x

' Se ordena la matriz por esta (pen-última) columna
' We sort the array with the concatenated column as key
Call QuickSort(Array3, UBound(Array3, 2) - 1, LBound(Array3),
UBound(Array3), True)


' En la última columna se marcan los registros repetidos
' In the last column we identify and mark duplicates
For x = 2 To UBound(Array3)
If Array3(x, UBound(Array3, 2) - 1) = Array3(x - 1, UBound(Array3,
2) - 1) Then
Array3(x, UBound(Array3, 2)) = "REPETIDO"
End If
Next x

' Se ordena la matriz por orden de registros repetidos y no repetidos.
' We sort the array with the DuplicateIdentify column as key
Call QuickSort(Array3, UBound(Array3, 2), LBound(Array3),
UBound(Array3), True)

' Se cuenta la cantidad de registros NO repetidos, que va a ser el ubound
de la matriz limpia de duplicados.
' We determine the amount of non-duplicates in order to ReDim the clean
array
x = 1
Do Until Array3(x, UBound(Array3, 2)) < Empty Or x = UBound(Array3, 1)
x = x + 1
Loop

' El UBound de ConsolidatedArray depende de que haya o no duplicados.
' ConsolidatedArray's UBound depends on wheather or not there are
duplicated registers.
Dim ThereAreDuplicates As Boolean
ThereAreDuplicates = False
If Not x = UBound(Array3, 1) Then
ThereAreDuplicates = True
ElseIf x = UBound(Array3, 1) Then
If Array3(UBound(Array3, 1), UBound(Array3, 2)) = "REPETIDO" Then
ThereAreDuplicates = True
End If
End If
' Array4 es la matriz limpia de duplicados. En vista de que los duplicados
ya fueron excluÃ*dos, ya no se necesitan las dos columnas extras.
' Array4 is the duplicate-free array. Since the duplicates have been
identified and sent to the end, we no longer need the extra columns.
If ThereAreDuplicates = True Then
ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
ElseIf ThereAreDuplicates = False Then
ReDim ConsolidatedArray(1 To x, 1 To UBound(Array2, 2))
End If
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x

End Sub

Append One Array to Another, and Consolidate
 

What were the fixes? I don't want to have to go through the code line by
line.

Alan Beban

Albert wrote:
You are right, my algorithm was flawed.
Here it is fixed.
Please tell me how it you find any more bugs.
Thx,
Albert

Sub ArrayConsolidator(Array1, Array2)

' Alberto Cattan Rozenfarb
'
' 17 de Noviembre de 2006

' Array1 y Array2 son dos arrays exógenos que pueden o no tener elementos
duplicados
' Array1 and Array2 are two exogenous arrays which may or may not have
duplicated registers
' Array1 = Range("A1", "L19")
' Array2 = Range("A20", "L42")
If Not UBound(Array1, 2) = UBound(Array2, 2) Then
MsgBox "La segunda dimensión de los dos arrays debe tener el mismo
UBound." & vbCrLf & "The second dimension for both arrays must have the same
UBound."
Exit Sub
End If

' Se crea Array3 que es una combinación de Array1 y Array2.
' Tiene 2 columnas extra para reconocer registros duplicados.
' We create Array3 which is a blunt combination of Array1 and Array2
' It has 2 extra columns which we will use to identify and eliminate
duplicates.
ReDim Array3(1 To UBound(Array1) + UBound(Array2), 1 To UBound(Array1,
2) + 2)
For x = 1 To UBound(Array1)
For Y = 1 To UBound(Array1, 2)
Array3(x, Y) = Array1(x, Y)
Next Y
Next x
For x = 1 To UBound(Array2)
For Y = 1 To UBound(Array1, 2)
Array3(UBound(Array1) + x, Y) = Array2(x, Y)
Next Y
Next x

' Se genera una columna con un Concatenado de todas las demás columnas.
' Esta es la llave para reconocer duplicados
' We generate a column which concatenates all the columns in arrays 1&2
' We will use this column to identify duplicates
For x = 1 To UBound(Array3)
For g = 1 To UBound(Array1, 2)
Array3(x, UBound(Array2, 2) + 1) = Array3(x, UBound(Array2, 2) +
1) & Array3(x, g)
Next g
Next x

' Se ordena la matriz por esta (pen-última) columna
' We sort the array with the concatenated column as key
Call QuickSort(Array3, UBound(Array3, 2) - 1, LBound(Array3),
UBound(Array3), True)


' En la última columna se marcan los registros repetidos
' In the last column we identify and mark duplicates
For x = 2 To UBound(Array3)
If Array3(x, UBound(Array3, 2) - 1) = Array3(x - 1, UBound(Array3,
2) - 1) Then
Array3(x, UBound(Array3, 2)) = "REPETIDO"
End If
Next x

' Se ordena la matriz por orden de registros repetidos y no repetidos.
' We sort the array with the DuplicateIdentify column as key
Call QuickSort(Array3, UBound(Array3, 2), LBound(Array3),
UBound(Array3), True)

' Se cuenta la cantidad de registros NO repetidos, que va a ser el ubound
de la matriz limpia de duplicados.
' We determine the amount of non-duplicates in order to ReDim the clean
array
x = 1
Do Until Array3(x, UBound(Array3, 2)) < Empty Or x = UBound(Array3, 1)
x = x + 1
Loop

' El UBound de ConsolidatedArray depende de que haya o no duplicados.
' ConsolidatedArray's UBound depends on wheather or not there are
duplicated registers.
Dim ThereAreDuplicates As Boolean
ThereAreDuplicates = False
If Not x = UBound(Array3, 1) Then
ThereAreDuplicates = True
ElseIf x = UBound(Array3, 1) Then
If Array3(UBound(Array3, 1), UBound(Array3, 2)) = "REPETIDO" Then
ThereAreDuplicates = True
End If
End If
' Array4 es la matriz limpia de duplicados. En vista de que los duplicados
ya fueron excluÃ*dos, ya no se necesitan las dos columnas extras.
' Array4 is the duplicate-free array. Since the duplicates have been
identified and sent to the end, we no longer need the extra columns.
If ThereAreDuplicates = True Then
ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
ElseIf ThereAreDuplicates = False Then
ReDim ConsolidatedArray(1 To x, 1 To UBound(Array2, 2))
End If
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x

End Sub

Append One Array to Another, and Consolidate
 

Sorry,
I meant:
Please tell me if you find any more bugs.
Albert C.

Append One Array to Another, and Consolidate
 

Albert wrote:
Sorry,
I meant:
Please tell me if you find any more bugs.
Albert C.
Yes, I know. But I want you to tell me what changes you made so that I
don't have to search through line by line to find those changes.

Alan Beban

Append One Array to Another, and Consolidate
 

I replaced the very end with this:

' El UBound de ConsolidatedArray depende de que haya o no duplicados.
' ConsolidatedArray's UBound depends on wheather or not there are
duplicated registers.
Dim ThereAreDuplicates As Boolean
ThereAreDuplicates = False
If Not x = UBound(Array3, 1) Then
ThereAreDuplicates = True
ElseIf x = UBound(Array3, 1) Then
If Array3(UBound(Array3, 1), UBound(Array3, 2)) = "REPETIDO" Then
ThereAreDuplicates = True
End If
End If
' Array4 es la matriz limpia de duplicados. En vista de que los duplicados
ya fueron excluÃ*dos, ya no se necesitan las dos columnas extras.
' Array4 is the duplicate-free array. Since the duplicates have been
identified and sent to the end, we no longer need the extra columns.
If ThereAreDuplicates = True Then
ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
ElseIf ThereAreDuplicates = False Then
ReDim ConsolidatedArray(1 To x, 1 To UBound(Array2, 2))
End If
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x

End sub

Append One Array to Another, and Consolidate
 

I replaced some stuff at the end. You only have to go throught the last two
"paragraphs".

Append One Array to Another, and Consolidate
 

What do you mean by "the very end"? Exactly which lines were replaced?

Alan Beban

Albert wrote:
I replaced the very end with this:

' El UBound de ConsolidatedArray depende de que haya o no duplicados.
' ConsolidatedArray's UBound depends on wheather or not there are
duplicated registers.
Dim ThereAreDuplicates As Boolean
ThereAreDuplicates = False
If Not x = UBound(Array3, 1) Then
ThereAreDuplicates = True
ElseIf x = UBound(Array3, 1) Then
If Array3(UBound(Array3, 1), UBound(Array3, 2)) = "REPETIDO" Then
ThereAreDuplicates = True
End If
End If
' Array4 es la matriz limpia de duplicados. En vista de que los duplicados
ya fueron excluÃ*dos, ya no se necesitan las dos columnas extras.
' Array4 is the duplicate-free array. Since the duplicates have been
identified and sent to the end, we no longer need the extra columns.
If ThereAreDuplicates = True Then
ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
ElseIf ThereAreDuplicates = False Then
ReDim ConsolidatedArray(1 To x, 1 To UBound(Array2, 2))
End If
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x

End sub

Append One Array to Another, and Consolidate
 

Check out the final 5 lines of the procedure...
I replaced this (old):

ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x

with this (new):

Dim ThereAreDuplicates As Boolean
ThereAreDuplicates = False
If Not x = UBound(Array3, 1) Then
ThereAreDuplicates = True
ElseIf x = UBound(Array3, 1) Then
If Array3(UBound(Array3, 1), UBound(Array3, 2)) = "REPETIDO" Then
ThereAreDuplicates = True
End If
End If
If ThereAreDuplicates = True Then
ReDim ConsolidatedArray(1 To x - 1, 1 To UBound(Array2, 2))
ElseIf ThereAreDuplicates = False Then
ReDim ConsolidatedArray(1 To x, 1 To UBound(Array2, 2))
End If
For x = 1 To UBound(ConsolidatedArray, 1)
For Y = 1 To UBound(ConsolidatedArray, 2)
ConsolidatedArray(x, Y) = Array3(x, Y)
Next Y
Next x