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?!

Find more posts tagged with

Accepted answers

All comments

Arnold A. Noche

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

DKazatsky2

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

1845sarah

Thankyou!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.