Creating a leaderboard from a list of nominations

I have an intake sheet with a list of nomination names and a nomination date. This will be populated via a form so will be continually updating. From this sheet I need to create a leaderboard showing the top 3 nomination names.
I have created a helper column to count the times the name appears:
=COUNTIFS([Nominator Name]:[Nominator Name], @cell = [Nominator Name]@row)
I have created another sheet called Leaderboard to pull in the 'Leaderboard Positions' of 1, 2, 3.
I have used the below cross sheet formula to pull in the Nominator Name
=INDEX({Source Sheet | Name}, MATCH(LARGE({Source Sheet | Helper}, [Leaderboard Position]@row), {Source Sheet | Helper}, 0))
And for the count of Nominations, I have used =INDEX({Source Sheet | Helper}, MATCH([Nominator Name]@row, {Source Sheet | Name}, 0))
I have applied a column formula and expected it to work!
However, when I expand the positions to 15, this happens:
What simple logic am I missing?!
Answers
-
I built something similar where I captured a name and their top 5 strengths in no particular order. I only had two sheets but tons of metric reports to display the chart I wanted displayed on a common dashboard.
Have a look while I think about this more.
www.arnoldnoche.com/showcase
-
Hi @1845sarah,
You just need to add a "DISTINCT" portion to the formula in the "Nominator Name" column and you should be all set.
=INDEX({Source Sheet | Name}, MATCH(LARGE(DISTINCT({Source Sheet | Helper}), [Leaderboard Position]@row), {Source Sheet | Helper}, 0))
Hope this helps,
Dave
-
Thankyou!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.4K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 492 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!