Append One Array to Another, and Consolidate
 

I ran it with the new one. The result was

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

I.e., it simply merged the two arrays with the rows rearranged and
eliminated no duplicates.

Did you test it before reposting?

Alan Beban

Albert wrote:
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

Append One Array to Another, and Consolidate
 

Sure I tested it.
Just re-tested it.
Works great here.

"Alan Beban" wrote:

I ran it with the new one. The result was

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

I.e., it simply merged the two arrays with the rows rearranged and
eliminated no duplicates.

Did you test it before reposting?

Alan Beban

Albert wrote:
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

Append One Array to Another, and Consolidate
 

Albert wrote:
Sure I tested it.
Just re-tested it.
Works great here.

I ran the programs with ranges A1:D3 and A5:D7, and put the output in
A11:A16.

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

Can you run it on that test data and report your results.

Thanks,
Alan Beban

Append One Array to Another, and Consolidate
 

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

Append One Array to Another, and Consolidate
 

Albert wrote:
29 30 31 11
5 1 7 3
9 10 11 12
1 2 3 4
21 9 21 24
25 26 10 28

I thought duplicates were supposed to be eliminated from the
consolidated array!???

Alan Beban

Append One Array to Another, and Consolidate
 

Albert wrote:
29 30 31 11
5 1 7 3
9 10 11 12
1 2 3 4
21 9 21 24
25 26 10 28

Oh, I get it. Although the original poster mentioned consolidating
duplicate "elements", your algorithm consolidates duplicate rows.

Alan Beban

Append One Array to Another, and Consolidate
 

Yeah, I guess we should have gotten that clear from the start.

"Alan Beban" wrote:

Albert wrote:
29 30 31 11
5 1 7 3
9 10 11 12
1 2 3 4
21 9 21 24
25 26 10 28

Oh, I get it. Although the original poster mentioned consolidating
duplicate "elements", your algorithm consolidates duplicate rows.

Alan Beban

Append One Array to Another, and Consolidate
 

Albert,

If the functions in the freely downloadable file at
http://home.pacbell.net/beban were available to your workbook, here is
an example of another way the task might be accomplished (variables are
not declared):

Sub CallDeleteDuplicateRows()
arr1 = Range("A1:D3")
arr2 = Range("A5:D7")
iRows = UBound(arr1) - LBound(arr1) + 1 _
+ UBound(arr2) - LBound(arr2) + 1
iCols = UBound(arr1, 2) - LBound(arr1, 2) + 1
arr = MakeArray(arr1, arr2, 1)
arr = ArrayReshape(arr, iRows, iCols)
Range("A21").Resize(UBound(arr) - LBound(arr) + 1, _
UBound(arr, 2) - LBound(arr, 2) + 1).Value = _
DeleteDuplicateRows(arr)
End Sub

Function DeleteDuplicateRows(arr)
For i = UBound(arr) To 2 Step -1
For j = i To 2 Step -1
If RowsEqual(Application.Index(arr, i, 0), _
Application.Index(arr, j - 1, 0)) Then
arr = DeleteRow(arr, i)
Exit For
End If
Next j
Next i
DeleteDuplicateRows = arr
End Function


Albert wrote:
Yeah, I guess we should have gotten that clear from the start.

"Alan Beban" wrote:

Oh, I get it. Although the original poster mentioned consolidating
duplicate "elements", your algorithm consolidates duplicate rows.

Alan Beban