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.

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?

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?

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?

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

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

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

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?

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

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

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

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

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

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"

:-(

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

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"

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

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

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.

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.

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.

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.

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

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

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