unique, sort, top 5

[orsm6]

hi all - I'm in a jam and need some support. I am making a production summary dashboard for the team and I am asked to show the top 5 downtime reasons, durations, occurrences and present them highest to lowest so i can graph it. But not sure how it is done.

I have uploaded an image showing the column headers and the respective column letter it sits in, but was hoping someone could help me pull out the top 5 DURATIONS sorted highest to lowest but also marry it up to the REASON NAME.

there could be duplicate durations which is why I am also at a loss how to do this.
not even sure if unique is the right way to go about it.


TIA everyone

 

[orsm6]

Hi all - I stumbled across another website that actually has given me the answer, so I'll share it here for others.

take a look at the image i uploaded, but essentially it worked like this:

column 1: type in the number 1 to whatever you need (in my case was 5)
column 2: =LARGE($H$43:$H$1501,ROWS(H$43:H43)) adjusted to your range
column 3: =INDEX($C$43:$C$1501,MATCH(LARGE($H$43:$H$1501,L43),$H$43:$H$1501,0)) adjusted to your range

this also appears to work fine for duplicates

 

[Fluff]

As you have 365 you could do that with a single formula that will spill

=TAKE(SORT(HSTACK(H43:H1501,C43:C1501),-1,-1),5)

 

[orsm6]

As you have 365 you could do that with a single formula that will spill

=TAKE(SORT(HSTACK(H43:H1501,C43:C1501),-1,-1),5)

Hi Fluff - thank you for your help.

I tried the formula (assuming i could paste it anywhere on the same sheet) and it resulted in #VALUE!

 

[Fluff]

Oops had a stray - sign in there. It should be

=TAKE(SORT(HSTACK(H43:H1501,C43:C1501),1,-1),5)

 

[orsm6]

Oops had a stray - sign in there. It should be

=TAKE(SORT(HSTACK(H43:H1501,C43:C1501),1,-1),5)

thank you - I actually added another column which has come in handy.

that was a great formula, thank you.

 

[Fluff]

You're welcome & thanks for the feedback.