Option Explicit
Sub test()
Dim arr, i, j, m, n, p
arr = Range("a1:b" & [a1].End(xlDown).Row + 1)
ReDim brr(1 To UBound(arr, 1), 1 To 50)
Call dsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 1)
For i = 1 To UBound(arr, 1) - 1
If arr(i, 1) <> arr(i + 1, 1) Then
Call dsort(arr, p + 1, i, 1, UBound(arr, 2), 2)
m = m + 1: n = 1: brr(m, n) = arr(i, 1)
For j = p + 1 To i
If arr(j, 1) <> arr(j + 1, 1) Or arr(j, 2) <> arr(j + 1, 2) Then
n = n + 1: brr(m, n) = arr(p + 1, 2)
p = j
End If
Next
p = i
End If
Next
[h8].Resize(m, UBound(brr, 2)) = brr
End Sub
Function dsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = i + 1 To last
If arr(i, key) > arr(j, key) Then
For k = left To right
t = arr(i, k): arr(i, k) = arr(j, k): arr(j, k) = t
Next
End If
Next
Next
End Function |