'数据量大考虑用字典,这里key数组非常小字典都可以不用
'如果无法找到匹配序列按原序排在最后
'另外你这示例结果为非稳定排序,,,
Option Explicit
Sub test()
Dim arr, brr, i, j
arr = Sheets("sheet2").[a1].CurrentRegion.Resize(, 5)
brr = Sheets("sheet1").[a1].CurrentRegion.Resize(, 2)
For i = 2 To UBound(brr, 1)
brr(i, 2) = i
Next
For i = 2 To UBound(arr, 1)
arr(i, 5) = UBound(brr, 1) + 1
For j = 2 To UBound(brr, 1)
If arr(i, 1) = brr(j, 1) Then arr(i, 5) = brr(j, 2): Exit For
Next
Next
Call bsort(arr, 2, UBound(arr, 1), 1, UBound(arr, 2), 5)
[j1].Resize(UBound(arr, 1), UBound(arr, 2) - 1) = arr
End Sub
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) > arr(j + 1, key) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next
Next
End Function |