Filter formula Top N values. Duplicates ranked underneath - how?

[Minilin]

Hiya,

I have a table of risks and have got them into a top 5 report which works fine as long as there aren't two risks with the same value. As this then causes a spill error as it tries to stick the two with the same value into the cells surrounding it.

I have used the following formulas (left to right) in the report:
=FILTER(Table1 [ Risk/Issue Description] ,Table1[Risk Total]=LARGE(Table1[Risk Total],B4))
=LARGE(Table1[Risk Total],B4)
=FILTER(Table1 [ Mitigation Strategy] ,Table1[Risk Total]=LARGE(Table1[Risk Total],B4))

What can I add to the above formula that would prevent this from happening?

Rank 1 and 2 could have the same risk total, I wouldn't want it going into the same cell.

 

[Fluff]

How about using this one formula

=index(sort(Table1[[Risk/Issue Description]:[Mitigation Strategy]],4,-1),SEQUENCE(5),{1,4,5})

 

[Minilin]

How about using this one formula

=index(sort(Table1[[Risk/Issue Description]:[Mitigation Strategy]],4,-1),SEQUENCE(5),{1,4,5})

Hi Fluff,

That works and copies across the Risk Description and Risk Total but the Mitigation Strategy info doesn't appear.

Is it possible for this to come across as well?

Cheers

 

[Fluff]

the Mitigation Strategy info doesn't appear.

That's because it's in col E which is hidden. Can you live with it in col E, or does it have to be col F?

 

[Minilin]

That's because it's in col E which is hidden. Can you live with it in col E, or does it have to be col F?

Oh my days! What a thicky. Didn't even realise I had hidden that column!

Thank you this all works perfectly. Life saver!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Minilin]

You're welcome & thanks for the feedback.

Could I do the same for my Issues report?

I used the same Filter formula as I had for the risks before but I based it on the value I assigned to Low ,Medium, High priority (1,2,3) in Column L instead of the Risk Total.

 

[Fluff]

Yes you could use

=index(sort(Table1[[Risk/Issue Description]:[Priority '#]],7,-1),SEQUENCE(5),{1,7,5})

 

[Minilin]

Yes you could use

=index(sort(Table1[[Risk/Issue Description]:[Priority '#]],7,-1),SEQUENCE(5),{1,7,5})

This still seems to be returning the risks rather than the issues.

I would have expected it to be: test 2, test5 then test 7.

 

[Fluff]

Which columns do you want to return?