Ordered Count for each Unique Entry

[erahi]

I have data in longitudinal format for patients. Each row denotes a treatment with patient column for Patient ID and date of treatment. I need a formula to work out an ordered count for each patient for their first treatment, second treatment, third treatment etc.

 

[Yongle]

[TABLE="width: 505"]
<tbody>[TR]
[TD="align: center"]Patient ID
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Ordered Count[/TD]
[TD="align: center"]Explanation
[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 1[/TD]
[TD="align: center"]25/05/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Patient 1 treatment 1[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 3[/TD]
[TD="align: center"]28/05/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Patient 3 treatment 1[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 1[/TD]
[TD="align: center"]03/06/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Patient 1 treatment 2[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 3[/TD]
[TD="align: center"]15/06/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Patient 3 treatment 2[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 4[/TD]
[TD="align: center"]18/07/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Patient 4 treatment 1[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 1[/TD]
[TD="align: center"]20/07/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Patient 1 treatment 3[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 1[/TD]
[TD="align: center"]23/07/2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Patient 1 treatment 4[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 4[/TD]
[TD="align: center"]27/07/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Patient 4 treatment 2[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 3[/TD]
[TD="align: center"]15/08/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Patient 3 treatment 3[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 2[/TD]
[TD="align: center"]16/08/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Patient 2 treatment 1[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 1[/TD]
[TD="align: center"]10/09/2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Patient 1 treatment 5[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 2[/TD]
[TD="align: center"]01/10/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Patient 2 treatment 2[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 3[/TD]
[TD="align: center"]12/10/2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Patient 3 treatment 4[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 1[/TD]
[TD="align: center"]01/11/2017[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Patient 1 treatment 6[/TD]
[/TR]
[TR]
[TD="align: center"]Patient 2[/TD]
[TD="align: center"]04/11/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Patient 2 treatment 3[/TD]
[/TR]
</tbody>[/TABLE]

The data can be sorted in any way, the formula continues to work

Formula in C2 copied down
=COUNTIFS(A:A,A2,B:B,"<"&B2)+1

Formula in D2 copied down
=A2&" treatment " &COUNTIFS(A:A,A2,B:B,"<"&B2)+1

 

[erahi]

Perfect! Exactly what I wanted, thanks so much