Append One Array to Another, and Consolidate

[Guest]

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.

 

[Morris]

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?

 

[NickHK]

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

 

[Guest]

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]

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

 

[NickHK]

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

 

[Guest]

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

 

[NickHK]

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

 

[Guest]

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).

 

[Alan Beban]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Helmut Weber]

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"

 

[Helmut Weber]

:-(

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

 

[Helmut Weber]

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"

 

[Helmut Weber]

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

 

[Guest]

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.

 

[Guest]

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