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€$

 

[combo]

With a helper column =COUNTIF($A$5:$A$20000,A5) copied down, you can filter "top 10" (and change 10 to 25), the list contains all members that have the same count as 25th.

combo

 

[PWD]

Hi combo. Interesting, but only inasmuch as even though I changed 10 to 25 it has in fact filtered on the top 40 rows - but this is all the same patient (I got the original formula wrong it should have been from $A$2) as he has had 40 attendances - so I guess that makes sense.

So he's my top attender with another patient at 31 and several in the 20's. Just clicking on the filter and counting with my finger on the screen, 7 visits is the 25th most frequent BUT I don't know how many of those visit summaries occur for more than one patient. Chances are that those numbers aren't unique to one patient.

Many thanks,
D€$

 

[SkipVought]

You could use MS Query, providing that you have a valid table on a sheet, with ONLY one table, with one row of unique headings in row 1 and no empty rows within the table.

will need to know your table structure, but the SQL will look something like...
[pre]
select [patient id], Count([patient id]
From [YourWorkbookPath.YourSheetName$]
Group by [patient id]
[/pre]

Then you do a Top whatever on the resultset.

OR you could do a PivotTable and accomplish the same thing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

What office year+version do yuu have and what office year has end user (i.e. a person that will generate final report)?

combo

 

[PWD]

We're using Office Professional Plus 2010.



Many thanks,
D€$

 

[PWD]

I was thinking about Pivot Tables but can't see a way to sort the Count of Attendances column - and then how to remove those after the 25th most frequent patient.

Many thanks,
D€$

 

[combo]

Ooops, I see, excel 2010.

You can try with excel Power Query add-in, assumed that you have table Patients and ID as patients ID.
First, add table to Power Query, I changed query name to Patients, the first automatic step after table loading were type conversions. Next duplicate it (left pane list of queries, right-click query name), one will be used to create rank, the second will be base table. I changed name of copy to Patients_1.

Query Patients_1 (rank):
1) Group rows: group by Id, add column Freq, count rows,
2) sort rows, by Freq, descending,
3) add index, from 1, step 1
Advanced query syntax:
[pre]let
Source = Excel.CurrentWorkbook(){[Name="Patients"]}[Content],
#"Change Type" = Table.TransformColumnTypes(Źródło,{{"ID", type text}, {"Val", Int64.Type}}),
#"Group Rows" = Table.Group(#"Change Type", {"ID"}, {{"Freq", each Table.RowCount(_), type number}}),
#"Sort Rows" = Table.Sort(#"Group Rows",{{"Freq", Order.Descending}}),
#"Add Index" = Table.AddIndexColumn(#"Sort Rows", "Index", 1, 1)
in
#"Add Index"[/pre]

Query Patients:
1) link with Patients_1 (via append), ID field,
2) add linked column (Index as Rank).
Advanced query syntax:
[pre]let
Source = Excel.CurrentWorkbook(){[Name="Patients"]}[Content],
#"Change Type" = Table.TransformColumnTypes(Źródło,{{"ID", type text}, {"x", Int64.Type}}),
#"Join Queries" = Table.NestedJoin(#"Change Type",{"ID"},Patients_1,{"ID"},"New column",JoinKind.LeftOuter),
#"Add Linked Column" = Table.AggregateTableColumn(#"Join Queries", "New column", {{"Index", List.Average, "Rank"}})
in
#"Add Linked Column"[/pre]

This query returns all visits with rank, but no problem to add last step with filtering Rank before returning data to worksheet.


combo

 

[PWD]

Unfortunately I don't have sufficient privileges on my PC to install the Power Query add-in.

Many thanks,
D€$

 

[PWD]

OK, I'm trying just to get all the data into a Pivot Table but can't find a way to sort the No. of Attendances column. If I click in its Grand Total cell and click sort descending I get an error message

Cannot determine which PivotTable field to sort by.

Any ideas?

Many thanks,
D€$

 

[combo]

Right-click any row item, settings in sort>more sort options.

combo

 

[PWD]

Thanks, I'd just tried that after reading a post on another site. I'd tried it on the name or GP columns but it only appears to work on the Patient ID column as that's the only one with unique values. I can then drag the cells in the 'Total' column until I get to 25. This would then give me the value from where to delete data from the source sheet or just filter out these others. Seems rather clunky though!

Many thanks,
D€$

 

[zelgar]

In order to keep data integrity wouldn't a macro doing the following be best:
1. Make a new report sheet/delete the data on the report sheet
2. Copy the data onto the report sheet
3. Remove duplicates of the data
4. Insert the COUNTIF statement
5. Sort by the Column with the COUNTIF statement
6. Delete the data after the top 25 records

 

[combo]

How is your data and PT look like? In case of pivot table with one row field (haven't checked more) and any summaries in data area, it's not a problem to sort basing on any data field, just select row item instead of header.

combo

 

[SkipVought]

Can you upload your workbook or a pared down representative version?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Apologies for the break in addressing this but I got pulled on to other things. The reference column is just the last two of the actual reference and in the real world these will all be unique. As you can see the final column is sorted by visits - descending. Now it probably is the reality of the data that the 25th person has the same number of visits as all those below them (we exclude those with only 1 or 2) and so we will have to add all the data to be pivoted but I still think it would be good to be able to identify that person.

Many thanks,
D€$

 

[SkipVought]

Okay, I Pivoted and it was not satisfactory. Stand by.

 

[PWD]

Thanks Skip. This will be the raw data for a Pivot table.

Many thanks,
D€$

 

[SkipVought]

first I used Data > Remove duplicates and I modified the data to make another 6 visit Ref.
[pre]
Ref Visits
78 6
95 6
45 4
36 4
55 4
66 4
51 3
28 3
61 3
98 3
15 3
56 3
96 3
84 3
01 3
99 3
74 3
18 3
33 3
72 3
00 3
50 3
35 3
92 3
04 3
87 3
27 3
21 3
31 3
12 3
90 3
22 3
02 3
[/pre]

This table is on sheet New.

Then I added a Cumulative column, formula [tt] =IF(ISNUMBER(C1),C1,0)+B2[/tt]
[pre]
Ref Visits Cumulative
78 6 6
95 6 12
45 4 16
36 4 20
55 4 24
66 4 28
51 3 31
28 3 34
61 3 37
98 3 40
15 3 43
56 3 46
96 3 49
84 3 52
[/pre]

Then I used MS Query to get this
[pre]
Ref Visits Cumulative
78 6 6
95 6 12
45 4 16
36 4 20
55 4 24
66 4 28
[/pre]
SQL

SELECT `New$`.Ref, `New$`.Visits, `New$`.Cumulative
FROM `C:\Users\Skip\Downloads\MR09_–_Top_25_Lists_Test (1).xlsx`.`New$` `New$`

where `New$`.Cumulative <= (select MIN(a.Cumulative) 
FROM `C:\Users\Skip\Downloads\MR09_–_Top_25_Lists_Test (1).xlsx`.`New$` a 
where a.Cumulative >= 25)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, thanks for this. I think I may have muddied the waters along the way. What we need to do is use the data up to and including the 25th person. This will be used in the Pivot table so we need to include all their rows. The person with 6 visits will have 6 lines; 5 visits 5 lines etc. (There will most likely be more than one person per visit count.) Currently they use the Mk1 eyeball and count up for every change in Column A until the get to 25. Rather tedious. The rest of the rows are deleted and all the other rows are used as the data range for the Pivot table. I don't really want to introduce VBA into this for them.

Many thanks,
D€$