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

[Edward_Les]

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:

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

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Expected Return:
Top 3 FY 15
1. Disorderly Conduct
2. Hostile Work Environment
3. Job Duties

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

---

 

[Eric W]

Here's one way:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I
1
2	Open FY	Closed FY	Category	SubCategory				Open FY	Top 3
3	FY15	FY15	Employment Practices	Corrective Action				FY15	Job Duties
4	FY15	FY16	Workplace Violence	Disorderly Conduct					Hostile Work Environment
5	FY15	FY16	Workplace Violence	Disorderly Conduct					Disorderly Conduct
6	FY15	FY15	Discrimination	Hostile Work Environment
7	FY15	FY15	Discrimination	Hostile Work Environment
8	FY15	FY15	Harassment	Hostile Work Environment (Sexual)
9	FY15	FY15	Management Standards of Behavior	Inappropriate Language
10	FY15	FY15	Work Assignment	Job Bid/Posting
11	FY15	FY16	Work Assignment	Job Duties
12	FY15	FY16	Work Assignment	Job Duties
13	FY15	FY15	Discrimination	Race
14	FY15	FY15	Harassment	Sexual Harassment
15	FY16	FY16	Hourly Team Member	Inappropriate Language
16	FY16	FY17	Management Standards of Behavior	Inappropriate Language
17	FY16	FY16	Harassment	Hostile Work Environment (Sexual)
18	FY16	FY16	Work Assignment	Job Bid/Posting
19	FY16	FY17	Workplace Violence	Disorderly Conduct
20	FY16	FY16	Work Assignment	Job Duties
21	FY16	FY16	Safeguarding of Assets	Theft of time
22	FY16	FY17	Employment Practices	Policy Inquiry/Clarification
23	FY16	FY16	Harassment	Race
24	FY16	FY16	Health & Safety	PPE/Supplies
25	FY16	FY16	Work Assignment	Job Bid/Posting
26	FY16	FY16	Safeguarding of Assets	Theft of time
27	FY16	FY16	Work Assignment	Job Bid/Posting
28	FY16	FY16	Workplace Violence	Disorderly Conduct
29	FY16	FY17	Work Assignment	Job Duties
30	FY16	FY16	Hourly Team Member	Inappropriate Language
31	FY16	FY16	Management Standards of Behavior	Inappropriate Language
32	FY16	FY16	Harassment	Hostile Work Environment (Sexual)
33	FY16	FY16	Work Assignment	Job Bid/Posting
34	FY16	FY16	Workplace Violence	Disorderly Conduct
35	FY16	FY16	Work Assignment	Job Duties
36	FY16	FY17	Safeguarding of Assets	Theft of time
37	FY16	FY16	Employment Practices	Policy Inquiry/Clarification

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula I3 =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),"") <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

 

[Edward_Les]

Here's one way:

Thank you it works great!