Formula to show top performers with column title

Robert Harrison

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.

Genevieve P.

Hi @Robert Harrison

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

Genevieve P.

Hi @Robert Harrison

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

Robert Harrison

Genevieve,

You are freaking awesome.

It is exactly what I am looking for.

Genevieve P.

Haha, wonderful! I'm glad I could help. 🙂