sort dates

[Dan]

I have a list of 100 dates (some are duplicate) - how do I find the 5 most
recent unique dates?
Many thanks,
Dan

 

[Bernie Deitrick]

Dan,

For example, in cell C1:

=MAX(A1:A100)

In cell C2, array enter (enter using Ctrl-Shift-Enter)
=MAX(($A$1:$A$1000<C1)*$A$1:$A$1000)

and then copy C2 to C3:C5.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Dan,

I just realized that you are in programming....

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub Find5LatestDates()
Dim Latest(1 To 5) As Date
Dim myC As Range
Dim i As Integer
Dim j As Integer

For Each myC In Range("A1:A100")
For i = 1 To 5
If myC.Value = Latest(i) Then GoTo NextDate
Next i
For i = 1 To 5
If myC.Value > Latest(i) Then
For j = 4 To i Step -1
Latest(j + 1) = Latest(j)
Next j
Latest(i) = myC.Value
GoTo NextDate
End If
Next i
NextDate:
Next myC

For i = 1 To 5
MsgBox Format(Latest(i), "mm/dd/yyyy")
Next i

End Sub

 

[Bernd P]

Hello,

I suggest to steal code:

Sub Pfreq_Sample()
Range("B1:B5").FormulaArray = GSort(Pfreq(Range("A1:A30")), "D")
End Sub

Pfreq you can find here:
http://www.sulprobil.com/html/pfreq.html

GSort you can get here:
http://www.sulprobil.com/html/sort_vba.html

Regards,
Bernd