Find occurance with respect to count

hi,

I want to find the date based on the occurance count. pls see attached file.


thank you.

Try this:-

Sub MG19Nov03
Dim Rng As Range, Dn As Range, n As Long
Dim Ws As Worksheet
Dim Q
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Ws In Worksheets
    If Not Ws.Name = "Final Data" Then

With Ws
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
        For Each Dn In Rng
            If Not .Exists(Dn.Value) Then
                .Add Dn.Value, Array(1, Dn.Offset(, 1))
            Else
                Q = .Item(Dn.Value)
                Q(0) = Q(0) + 1
                Q(1) = Dn.Offset(, 1)
                .Item(Dn.Value) = Q
            End If
    Next Dn
    End If
Next Ws
Dim K
Dim c As Long
c = 1
 For Each K In .keys
    c = c + 1
    Sheets("Final Data").Cells(c, 1) = K
    Sheets("Final Data").Cells(c, 2) = .Item(K)(0)
    Sheets("Final Data").Cells(c, 3) = Format(.Item(K)(1), "dd/mm/yyyy")
Next K
End With

End Sub

Regards Mick

i wanted just excel formula .... no vba