Formula to show top performers with column title
I have created a metric sheet to show how many hours an employee spends training, per period, for management but would also like to show the top 3 performs, per period, as well. I know how to use the LARGE function to show the top 3 numbers but don't know how I can have them show what employee it is pulling the information from.
I will be using this information to create a report I can put on managements Dashboard.
Any help would be great.
Best Answer
-
Since you potentially could have duplicates for your Large values (ex. in P1 you have 1 and 1 as your top two), we won't be able to pull one single name to associate with each of these values, as they'll be seen as the same.
What I would do is use a JOIN(COLLECT formula to bring together all names that match the value of 1. This does mean you'll see repeated names when there's a tie, like so:
You'll notice that I also added a Top Row to this sheet which duplicates the column names. It's this top row that will be able to bring the names into your chart at the bottom.
My formula for the first Emp row under P1 is:
=JOIN(COLLECT(Genevieve1:Mirka1, Genevieve2:Mirka2, LARGE(Genevieve2:Mirka2, 1)), ", ")
The structure is:
=JOIN(COLLECT(Top Row, P Row, LARGE(P Row, 1)), ", ")
Then for the second Employee for that same row, P1, you'll just need to swap out the number in the LARGE function:
=JOIN(COLLECT(Top Row, P Row, LARGE(P Row, 2)), ", ")
3rd Employee, P1:
=JOIN(COLLECT(Top Row, P Row, LARGE(P Row, 2)), ", ")
Then to change it for P2, you just need to update the P Row reference in the formula, moving it down one row... so in mine:
=JOIN(COLLECT(Genevieve1:Mirka1, Genevieve3:Mirka3, LARGE(Genevieve3:Mirka3, 1)), ", ")
Let me know if this will work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Since you potentially could have duplicates for your Large values (ex. in P1 you have 1 and 1 as your top two), we won't be able to pull one single name to associate with each of these values, as they'll be seen as the same.
What I would do is use a JOIN(COLLECT formula to bring together all names that match the value of 1. This does mean you'll see repeated names when there's a tie, like so:
You'll notice that I also added a Top Row to this sheet which duplicates the column names. It's this top row that will be able to bring the names into your chart at the bottom.
My formula for the first Emp row under P1 is:
=JOIN(COLLECT(Genevieve1:Mirka1, Genevieve2:Mirka2, LARGE(Genevieve2:Mirka2, 1)), ", ")
The structure is:
=JOIN(COLLECT(Top Row, P Row, LARGE(P Row, 1)), ", ")
Then for the second Employee for that same row, P1, you'll just need to swap out the number in the LARGE function:
=JOIN(COLLECT(Top Row, P Row, LARGE(P Row, 2)), ", ")
3rd Employee, P1:
=JOIN(COLLECT(Top Row, P Row, LARGE(P Row, 2)), ", ")
Then to change it for P2, you just need to update the P Row reference in the formula, moving it down one row... so in mine:
=JOIN(COLLECT(Genevieve1:Mirka1, Genevieve3:Mirka3, LARGE(Genevieve3:Mirka3, 1)), ", ")
Let me know if this will work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve,
You are freaking awesome.
It is exactly what I am looking for.
-
Haha, wonderful! I'm glad I could help. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!