Mergeing two Arrays

[Paul W Smith]

Has anyone got any sample code for merging two sorted one dimensional arrays
into one sorted new one?

e.g.

A1 = (2, 4, 6, 8)
A2 = (2, 3, 4, 9, 10)

RESULT = (2, 3, 4, 6, 8, 9, 10)

 

[merjet]

How about putting both arrays in one column of a
worksheet, sorting it, then copying the column to
the final array.

HTH,
Merjet

 

[Dave Peterson]

John Walkenbach has some sample code at:

http://j-walk.com/ss/excel/tips/tip47.htm

That builds a list of unique values using a collection and then sorts that
collection.

You could do the same type of thing:

Option Explicit
Sub RemoveDuplicates()
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim Arr3 As Variant
Dim i As Long, j As Long
Dim Swap1, Swap2, Item
Dim NoDupes As Collection

Set NoDupes = New Collection

Arr1 = Array(2, 4, 6, 8)
Arr2 = Array(2, 3, 4, 9, 10)

On Error Resume Next
For i = LBound(Arr1) To UBound(Arr1)
NoDupes.Add Arr1(i), CStr(Arr1(i))
Next i
For i = LBound(Arr2) To UBound(Arr2)
NoDupes.Add Arr2(i), CStr(Arr2(i))
Next i
On Error GoTo 0

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

ReDim Arr3(1 To NoDupes.Count)
i = 0
For Each Item In NoDupes
i = i + 1
Arr3(i) = Item
Next Item

End Sub

 

[Dave Ring]

This will merge two zero-based arrays A1() and A2() into
zero-based array A3().

Dave Ring

Sub Merge(A1(), A2(), A3())
Dim N1&, N2&, N3&, I&, J&, K&

N1 = UBound(A1): N2 = UBound(A3): N3 = N1 + N2
Redim A3(0 to N3)
I = 0: J = 0: K = 0
Do
If A1(I) <= A2(J) Then
A3(K) = A1(I): I = I + 1: K = K + 1
If I > N1 Then
Do
A3(K) = A2(J): J = J + 1: K = K + 1
Loop Until J > N2
Exit Do
End If
Else
A3(K) = A2(J): J = J + 1: K = K + 1
If J > N2 Then
Do
A3(K) = A1(I): I = I + 1: K = K + 1
Loop Until I > N1
Exit Do
End If
End If
Loop
End Sub

 

[Dave Ring]

This will merge two sorted arrays of variant. If you want another data
type, just dimension A1 to A3 accordingly.

Dave Ring

Sub Merge(A1(), A2(), A3())
Dim I&, J&, K&, N1&, N2&, N3&

'merges sorted arrays A1 and A2 into A3
N1 = UBound(A1) + 1 'if array is 0 based
N2 = UBound(A2) + 1
N3 = N1 + N2
ReDim A3(0 To N3 - 1) 'again, 0 based
I = 0: J = 0: K = 0
Do
If A1(I) <= A2(J) Then
A3(K) = A1(I): I = I + 1: K = K + 1
If I = N1 Then 'A1 is used up, so
Do 'move the rest of A2
A3(K) = A1(J): J = J + 1: K = K + 1
Loop Until J = N2: Exit Do
End If
Else
A3(K) = A1(J): J = J + 1: K = K + 1
If J = N2 Then 'A2 is used up, so
Do 'move the rest of A1
A3(K) = A1(I): I = I + 1: K = K + 1
Loop Until I = N1: Exit Do
End If
End If
Loop
End Sub