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? ;-)🙄

Paul Newcome

Here you go...

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

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?