Excel 2010 Top 25 Patients Based On Number of Attendances

[PWD]

Good afternoon, I have a worksheet that details every A&E attendance coded by the patient ID in Column A and has a formula to determine their total number of attendances, =COUNTIF($A$5:$A$20000,A2) in Column J. So for every time the patient attended there's a row and a formula with their total number of attendances. Obviously more than one patient can have come in the same number of times.

My report has to list the top 25 attenders, showing each of their rows.

Can anyone think of a smart way of achieving this?

At present I think they just scroll down all the patient IDs until they get to the 25th one and delete all the subsequent rows. I had thought about trying something like Subtotalling but that just appears to have locked up Excel.

Many thanks,
D€$

 

[SkipVought]

What columns are in your PT?

If it's your 4 columns, then forget the PT. use a formula in the next adjacent column for Cumulative...
[tt]
=IF(ISNUMBER(E1),E1,0)+IF(A2=A1,0,D2)
[/tt]
Then simply filter on Cumulative Less Than 25
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Thanks Skip. I've agreed a solution with my users.

Add subtotals for each change in Reference Number; uncheck Summary below data.
Collapse to level 2.
Select the first subtotal reference number and drag down until reaching 'Count: 25'.
Expand that number's section and make a note of the COUNTIF number.
Remove the Subtotals.
Filter on COUNTIF number less than the noted COUNTIF number and delete those rows.

Many thanks,
D€$