
Option Explicit
Sub abc()
Dim a, b, i, m, t, d, p
a = Range("a2:e" & [a2].End(xlDown).Row + 1).Value
For i = 1 To UBound(a) - 1
a(i, 5) = a(i, 1) & a(i, 2) & a(i, 3)
Next
Call bsort(a, 1, UBound(a) - 1, 1, 5, 5)
ReDim pos(1 To UBound(a) - 1, 1 To 2)
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(a) - 1
If a(i, 5) <> a(i + 1, 5) Then
m = m + 1: d(a(i, 5)) = m
pos(m, 1) = p + 1: pos(m, 2) = i
p = i
End If
Next
b = Range("f2:i" & [f2].End(xlDown).Row).Value
For i = 1 To UBound(b)
t = b(i, 1) & b(i, 2) & b(i, 3)
If d.exists(t) Then
If pos(d(t), 1) <= pos(d(t), 2) Then
b(i, 4) = a(pos(d(t), 1), 4)
pos(d(t), 1) = pos(d(t), 1) + 1
Else
b(i, 4) = "已取完"
End If
Else
b(i, 4) = "无法定位"
End If
Next
[f2].Resize(UBound(b), 4) = b
End Sub
Function bsort(a, 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 a(j, key) > a(j + 1, key) Then
For k = left To right
t = a(j, k): a(j, k) = a(j + 1, k): a(j + 1, k) = t
Next
End If
Next
Next
End Function