Finding the Top 5 of text filled column by the different Years in the data!

E

Edward_Les

New Member
Joined
Oct 13, 2017
Messages
3
I would like help solving this problem, Finding the Top 5 of text filled column by the different Years in the data.

I have a set of data in one sheet that covers 3 Fiscal years FY15, FY16 and FY17 (Column named "Open_FY") and sub categories of issues (named SubCat) this are workplace issues like Hostile Work Environment, Dignity and Respect, Theft... so in another sheet I layout the data in tables and charts and one table is the top 5 subcategories by Open_FY...

I can get the Top 5 for the over all date with this formula: =INDEX(SubCat,MATCH(LARGE(FREQUENCY(MATCH(SubCat,SubCat,0),ROW(SubCat)-ROW('FY16-FY17 Recieved'!$M$1)+1)+ROW('Prepared Foods FY16-FY17.xlsx'!SubCat_Plus1)/10000,ROWS(R$109:R109)),FREQUENCY(MATCH(SubCat,SubCat,0),ROW(SubCat)-ROW('FY16-FY17 Recieved'!$M$1)+1)+ROW(SubCat_Plus1)/10000,0)))

But can't find a way to get the correct Top 5 by Open_FY, an ideas? Thank you!

Sample data:
[TABLE="width: 402"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]A 1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Open FY[/TD]
[TD]Closed FY[/TD]
[TD]Category[/TD]
[TD]SubCategory[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Employment Practices[/TD]
[TD]Corrective Action[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]FY15[/TD]
[TD]FY16[/TD]
[TD]Workplace Violence[/TD]
[TD]Disorderly Conduct[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]FY15[/TD]
[TD]FY16[/TD]
[TD]Workplace Violence[/TD]
[TD]Disorderly Conduct[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Discrimination[/TD]
[TD]Hostile Work Environment[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Discrimination[/TD]
[TD]Hostile Work Environment[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Harassment[/TD]
[TD]Hostile Work Environment (Sexual)[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Management Standards of Behavior[/TD]
[TD]Inappropriate Language[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Work Assignment[/TD]
[TD]Job Bid/Posting[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]FY15[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Duties[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]FY15[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Duties[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Discrimination[/TD]
[TD]Race[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]FY15[/TD]
[TD]FY15[/TD]
[TD]Harassment[/TD]
[TD]Sexual Harassment[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Hourly Team Member[/TD]
[TD]Inappropriate Language[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]FY16[/TD]
[TD]FY17[/TD]
[TD]Management Standards of Behavior[/TD]
[TD]Inappropriate Language[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Harassment[/TD]
[TD]Hostile Work Environment (Sexual)[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Bid/Posting[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]FY16[/TD]
[TD]FY17[/TD]
[TD]Workplace Violence[/TD]
[TD]Disorderly Conduct[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Duties[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Safeguarding of Assets[/TD]
[TD]Theft of time[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]FY16[/TD]
[TD]FY17[/TD]
[TD]Employment Practices[/TD]
[TD]Policy Inquiry/Clarification[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Harassment[/TD]
[TD]Race[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Health & Safety[/TD]
[TD]PPE/Supplies[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Bid/Posting[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Safeguarding of Assets[/TD]
[TD]Theft of time[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Bid/Posting[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Workplace Violence[/TD]
[TD]Disorderly Conduct[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]FY16[/TD]
[TD]FY17[/TD]
[TD]Work Assignment[/TD]
[TD]Job Duties[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Hourly Team Member[/TD]
[TD]Inappropriate Language[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Management Standards of Behavior[/TD]
[TD]Inappropriate Language[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Harassment[/TD]
[TD]Hostile Work Environment (Sexual)[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Bid/Posting[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Workplace Violence[/TD]
[TD]Disorderly Conduct[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Work Assignment[/TD]
[TD]Job Duties[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]FY16[/TD]
[TD]FY17[/TD]
[TD]Safeguarding of Assets[/TD]
[TD]Theft of time[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]FY16[/TD]
[TD]FY16[/TD]
[TD]Employment Practices[/TD]
[TD]Policy Inquiry/Clarification[/TD]
[/TR]
</tbody>[/TABLE]
Expected Return:
Top 3 FY 15
1. Disorderly Conduct
2. Hostile Work Environment
3. Job Duties

[TABLE="width: 338"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Here's one way:

Excel 2016 (Windows) 32 bit
ABCDEFGHI
Open FYClosed FYCategorySubCategoryOpen FYTop 3
FY15FY15Employment PracticesCorrective ActionFY15Job Duties
FY15FY16Workplace ViolenceDisorderly ConductHostile Work Environment
FY15FY16Workplace ViolenceDisorderly ConductDisorderly Conduct
FY15FY15DiscriminationHostile Work Environment
FY15FY15DiscriminationHostile Work Environment
FY15FY15HarassmentHostile Work Environment (Sexual)
FY15FY15Management Standards of BehaviorInappropriate Language
FY15FY15Work AssignmentJob Bid/Posting
FY15FY16Work AssignmentJob Duties
FY15FY16Work AssignmentJob Duties
FY15FY15DiscriminationRace
FY15FY15HarassmentSexual Harassment
FY16FY16Hourly Team MemberInappropriate Language
FY16FY17Management Standards of BehaviorInappropriate Language
FY16FY16HarassmentHostile Work Environment (Sexual)
FY16FY16Work AssignmentJob Bid/Posting
FY16FY17Workplace ViolenceDisorderly Conduct
FY16FY16Work AssignmentJob Duties
FY16FY16Safeguarding of AssetsTheft of time
FY16FY17Employment PracticesPolicy Inquiry/Clarification
FY16FY16HarassmentRace
FY16FY16Health & SafetyPPE/Supplies
FY16FY16Work AssignmentJob Bid/Posting
FY16FY16Safeguarding of AssetsTheft of time
FY16FY16Work AssignmentJob Bid/Posting
FY16FY16Workplace ViolenceDisorderly Conduct
FY16FY17Work AssignmentJob Duties
FY16FY16Hourly Team MemberInappropriate Language
FY16FY16Management Standards of BehaviorInappropriate Language
FY16FY16HarassmentHostile Work Environment (Sexual)
FY16FY16Work AssignmentJob Bid/Posting
FY16FY16Workplace ViolenceDisorderly Conduct
FY16FY16Work AssignmentJob Duties
FY16FY17Safeguarding of AssetsTheft of time
FY16FY16Employment PracticesPolicy Inquiry/Clarification

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I3[/TH]
[TD="align: left"]=IFERROR(INDEX($D$3:$D$37,MOD(LARGE(IF($A$3:$A$37=$H$3,IF(MATCH($A$3:$A$37&"|"&$D$3:$D$37,$A$3:$A$37&"|"&$D$3:$D$37,0)=ROW($A$3:$A$37)-ROW($A$3)+1,COUNTIFS($A$3:$A$37,$H$3,$D$3:$D$37,$D$3:$D$37)+(ROW($A$3:$A$37)-ROW($A$3)+1)/10000)),ROWS($I$3:$I3)),1)*10000),"")[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



In case of ties, the list will show the values in reverse order of incidence. I assume you can adjust the ranges to match your sheet and defined names.

Hope this works for you.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,743
Messages
6,180,687
Members
452,994
Latest member
Janick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top