Show 1st, 2nd, 3rd often entry of a column

Joerg Schmidt

Hi,

we use a sheet to collect incidents for the Facility Department (FM Tracker).

I want to show in a dashboard which are the incidents that occur most (rank 1, 2, 3)

In the sheet summary I already tried this:

=COUNTIFS(subject1:subject30); MAX(subject1:subject30); RYG:RYG; "rot") => #unparseable

Any ideas? ;-)🙄

Find more posts tagged with

Accepted answers

Paul Newcome

Here you go...

=JOIN(DISTINCT(COLLECT(Thema:Thema, Helper:Helper, @cell = LARGE(DISTINCT(Helper:Helper), 1))), ", ")

All comments

Paul Newcome

Are you able to provide a screenshot of the sheet with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

Joerg Schmidt

Hi Paul,

here comes the screenshot - hope it helps. In this picture you see f.e. 5x "aufzug" (=elevator).

I (simply) want to show in the sheet summary what issues occur most (rank 1,2,3). Any idea?

Paul Newcome

Ah. Ok. You are going to want to add in a helper column that shows the count of how many times each entry appears.

=COUNTIFS(Thema:Thema, THema@row)

Then you can use a formula along the lines of the below to pull the most frequent entry (or entries if there is a tie):

=JOIN(DISTINCT(COLLECT(Thema:Thema, Helper:Helper, @cell = LARGE(Helper:Helper, 1))), ", ")

Just change the 1 in the LARGE function to 2 to get the second most frequent, 3 for the 3rd most frequent, etc.

Depending on your region you may need to swap out the commas for semi-colons.

Joerg Schmidt

Hi Paul,

could you check this please. When I change 1 into 2 the result of the formula is the same.

It should be "Sonstiges" (6) instead of "Aufzug" (9) - right?

Paul Newcome

You are correct. I think I know the problem. Let me do a little more testing, and I will get back to you. Sorry about that.

Paul Newcome

Here you go...

=JOIN(DISTINCT(COLLECT(Thema:Thema, Helper:Helper, @cell = LARGE(DISTINCT(Helper:Helper), 1))), ", ")

Joerg Schmidt

and how it works...😀

Thank you so much !

Paul Newcome

Happy to help! 👍️

Dan Kadushin

Sorry to pull this post back from a year ago, but it possible to use JOIN(DISTINCT(COLLECT(Thema:Thema, Helper:Helper, @cell = LARGE(DISTINCT(Helper:Helper), 1))), ", ") with cross sheet references? Could I pull ranges from another sheet if I want to do metric collection on an independent metric grid sheet? I tried plugging it into my metrics sheet but it's returning blank cells (no errors, just blank).

Paul Newcome

@Dan Kadushin Why are you using the DISTINCT function within the LARGE function?

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.