Sub test()
Set d = CreateObject("scripting.dictionary")
With Sheet1
ar = .[a1].CurrentRegion
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
d(Trim(ar(i, 1))) = ""
End If
Next i
For Each k In d.keys
n = 0
ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) = k Then
n = n + 1
For j = 1 To UBound(ar, 2)
br(n, j) = ar(i, j)
Next j
End If
Next i
For i = 1 To n
For s = i + 1 To n
If DateValue(br(i, 2)) < DateValue(br(s, 2)) Then
For j = 1 To UBound(br, 2)
kk = br(i, j)
br(i, j) = br(s, j)
br(s, j) = kk
Next j
End If
Next s
Next i
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) = k And DateValue(ar(i, 2)) = DateValue(br(1, 2)) Then
ar(i, 3) = "max"
ElseIf Trim(ar(i, 1)) = k And DateValue(ar(i, 2)) = DateValue(br(n, 2)) Then
ar(i, 3) = "min"
End If
Next i
Next k
.[a1].CurrentRegion = ar
End With
End Sub
|