A pivot table could be used.
Otherwise dynamic arrays could be used in Office 365.
Try..................
1] For the column of "Parient ID", "Durg Name" and "Days' Supply" using the Excel build-in function as in :
Select A2:C19 >>Copy>> Select H13 >> Paste >> Remove Duplicates >> uncheck "My data has headers" >>OK
View attachment 63619
Then,
2] In "Total Fill Days' Supply" K13, formula copied down :
=J13*N13
3] In "First Fill Date" L13, formula copied down :
=AGGREGATE(15,6,D$2:D$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)
4] In "Last Fill Date" M13, formula copied down :
=AGGREGATE(14,6,D$2:D$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)
5] In "# Fills" N13, formula copied down :
=COUNTIFS(A:A,H13,B:B,I13)
Regards
Bosco
Formula solution to the column of "Parient ID", "Durg Name" and "Days' Supply"Hello Bosco,
I really appreciate your help. Just wonder whether it's possible to apply formula to the column of "Parient ID", "Durg Name" and "Days' Supply" as well? BTW, I'm using Excel 2016. It's a good chance for me to learn.
View attachment 63626
Formula solution to the column of "Parient ID", "Durg Name" and "Days' Supply"
View attachment 63630
1] For the columns of "Parient ID" and "Durg Name"
In H13, formula copied right to I13 and all copied down :
=IFERROR(INDEX(A$2:A$19,AGGREGATE(15,6,ROW($A$1:$A$19)/(MATCH($A$2:$A$19&$B$2:$B$19,$A$2:$A$19&$B$2:$B$19,0)=ROW($A$1:$A$19)),ROWS($1:1))),"")
2] In "Days' Supply" J13, formula copied down :
=AGGREGATE(15,6,C$2:C$19/(A$2:A$19=H13)/(B$2:B$19=I13),1)
Regards
Bosco