"Top 10" Text Entries in a Column vs Date

[Schnagglebeef]

Good morning,

I have built a basic dashboard to help with my Workforce Efficiency reporting.

On the raw data spreadsheet there is Column "G" which lists the names of an employee who has picked up an additional shift. There is also a date when they picked up this shift in Column "D".

Is there a way to capture a "top 10" on my dashboard from this column which lists the employees where their name appears most frequently in a given month?

Thank you,

Paul

 

[Oaktree]

Since you are on Office 365, you can invoke some of the newer functions (LET, UNIQUE, FILTER, TAKE, SORT, and HSTACK) to achieve this:

This assumes:
1) your data in columns D & G runs from rows 2:100
2) J2 has the first day of the given month (or selected timeframe)
3) J3 has the last day of the given month (or selected timeframe)

=LET(x,UNIQUE(INDEX(FILTER($D$2:$G$100,($D$2:$D$100<=$J$3)*($D$2:$D$100>=$J$2)),0,4)),TAKE(SORT(HSTACK(x,COUNTIFS($D$2:$D$100,"<="&J3,$D$2:$D$100,">="&$J$2,$G$2:$G$100,x)),2,-1),10))

 

[Schnagglebeef]

Since you are on Office 365, you can invoke some of the newer functions (LET, UNIQUE, FILTER, TAKE, SORT, and HSTACK) to achieve this:

This assumes:
1) your data in columns D & G runs from rows 2:100
2) J2 has the first day of the given month (or selected timeframe)
3) J3 has the last day of the given month (or selected timeframe)

=LET(x,UNIQUE(INDEX(FILTER($D$2:$G$100,($D$2:$D$100<=$J$3)*($D$2:$D$100>=$J$2)),0,4)),TAKE(SORT(HSTACK(x,COUNTIFS($D$2:$D$100,"<="&J3,$D$2:$D$100,">="&$J$2,$G$2:$G$100,x)),2,-1),10))

Hello,

Thank you for the suggestion - when I use the formula it displays as 0. Is there anything else I need to manually adjust?