How to append one array to Another, and Consolidate them in vba

[Tarani]

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 am thinking there might be a way to do it entirely within arrays, off the worksheet.

[Koman]

Go through the smaller array, check for each element if the values duplicate - if not - redo the big array by one, and add the just processed element.

[Leanne]

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 described, then redim back down to the number of actual used elements This assumes by "consolidate" you mean "discard" .

[Hollyn]

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.

[QUENBY]

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 comparative, and array redimensioning, and then do a loop throughout the array to split them back. It's just a loose idea, as I'd prefer to see the example itself.

[GALIENA]

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

[DanielaA]

The values are both strings (ticker symbols) and numbers associated with the tickers. They are pulled from columns on a worksheet. 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")).

[Essien]

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

[Bontu]

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

[Shurod]

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.

[Alfons]

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