Ok - you can give this a try..
Excel 2013/2016
| A | B | C | D | E | F | G |
---|
Service | percentage | Number of visits | Date | % | Service | | |
Cardiology | | | | | | | |
Medicine | Medicine | | | | | | |
Surgery | Cardiology | | | | | | |
Cardiology | | | | | | | |
Medicine | | | | | | | |
Surgery | | | | | | | |
other | | | | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]01-Apr-2019[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]25%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]other[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]01-Apr-2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16%[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]01-Apr-2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15%[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]16%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(
AGGREGATE(14,6,$B$2:$B$8/(($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))),ROWS(F$2:F2)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(
F2="","",INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1)/(($B$2:$B$8=F2)*($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))),COUNTIFS(F$2:F2,F2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Hopefully one last wrinkle. It gave the top 10 which is awesome. However, not sure how it is determined for a tie. So Dental, Podiatry, and Urology are at 8.90%. There aren't any other numbers after the 0. So is there a way to determine which one has the highest number of visits and give that percentage vs. the lower one.
[TABLE="width: 453"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]180 - (180) DENTAL[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]411 - (411) PODIATRY[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]414 - (414) UROLOGY[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]49[/TD]
[/TR]
</tbody>[/TABLE]
Podiatry ended up showing up on the report, but was hoping Dental would show up.
My final table looks like this:
[TABLE="width: 331"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Stop Code[/TD]
[TD]No Show Rate (Combined)[/TD]
[TD]No Shows (Combined)[/TD]
[/TR]
[TR]
[TD](560) SUB USE DISORDER GROUP[/TD]
[TD="align: right"]26.20%[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD](513) SUB USE DISORDER INDIV[/TD]
[TD="align: right"]21.30%[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD](550) MH CLINIC GROUP[/TD]
[TD="align: right"]16.80%[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD](502) MH CLINIC IND[/TD]
[TD="align: right"]16.50%[/TD]
[TD="align: right"]518[/TD]
[/TR]
[TR]
[TD](534) MH INTGRTD CARE IND[/TD]
[TD="align: right"]14.40%[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD](160) CLINICAL PHARMACY[/TD]
[TD="align: right"]13.90%[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD](408) OPTOMETRY[/TD]
[TD="align: right"]11.60%[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD](205) PHYSICAL THERAPY[/TD]
[TD="align: right"]11.60%[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD](323) PRIMARY CARE/MEDICINE[/TD]
[TD="align: right"]10.90%[/TD]
[TD="align: right"]659[/TD]
[/TR]
[TR]
[TD](411) PODIATRY[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]79
[/TD]
[/TR]
</tbody>[/TABLE]
My codes show as such:
[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B86[/TH]
[TD]=IF(C86="","",INDEX($R$6:$R$1162,AGGREGATE(14,6,(ROW($R$6:$R$1162)-MIN(ROW($R$6:$R$1162))+1)/(($W$6:$W$1162=C86)*($X$6:$X$1162>50)*($U$6:$U$1162=MAX($U$6:$U$1162))),COUNTIFS(C$86:C86,C86))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C86[/TH]
[TD]=IFERROR(AGGREGATE(14,6,$W$6:$W$1162/(($X$6:$X$1162>50)*$U$6:$U$1162=MAX($U$6:$U$1162)),ROWS(C$86:C86)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D86[/TH]
[TD]=IF(C86="","",INDEX($X$6:$X$1162,AGGREGATE(14,6,(ROW($X$6:$X$1162)-MIN(ROW($X$6:$X$1162))+1)/(($W$6:$W$1162=C86)*($X$6:$X$1162>50)*($U$6:$U$1162=MAX($U$6:$U$1162))),COUNTIFS(C$86:C86,C86))))[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 100%"]
<thead></thead><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]