Results 1 to 4 of 4

Thread: Formula to find highest unique values in a range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2013
    Location
    Manchester
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to find highest unique values in a range

    Hi All,

    I have a range of risks with risk scores between 1-25
    ("F2:F2460"), and
    details about each of these risks within the range ("E2:E2460")

    I need to provide a list which will return the top 'X' amount of risks depending on what the risk scores are. I need to show a minimum of 5, however if there are further risks which score the same as the 5th highest risk, they would also show, so I could have maybe 12,13,14 etc. that I need to display.

    I have a hospital name in Cell H1.

    I have a formula in Cell I1 which identifies how many instances of the highest risk score there are, for the hospital in H1.

    Code:
    =SUM(IF(Risks!$A2:$A2460=$H$1,IF(Risks!$F$2:$F$2460>=LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),MIN(5,COUNT(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460)))),1)))
    I then have a formula in Cells I2:I6 which tells me what the score is:

    Code:
    =IF(ROWS($I$2:I2)>$I$1,"",LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),ROWS($I$2:I2)))
    And then a code in H2:H6 to give me the details:

    Code:
    =IF($I2="","",INDEX(Risks!$L$2:$L$2460,SMALL(IF(Risks!$A$2:$A$2460=$H$1,IF(Risks!$F$2:$F$2460=$I2,ROW(Risks!$A$2:$A$2460)-ROW($A$2)+1)),COUNTIFS($I$2:I2,I2))))
    Is anybody able to point me in the right direction at all as to whether this can be achieved? I'm assuming it can, I just cannot think how to go about it!

    Thanks in advance of any help!

    Simon
    Last edited by SimonGeoghegan; Aug 15th, 2019 at 10:12 AM.

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    230
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to find highest unique values in a range

    Simon,

    I think I'm onboard and it looks like you almost have it but shouldn't that final H2:H6 (which should probably be H2:H99 to catch duplicates of the last risk score)
    INDEX(Risks!$L$2:$L$2460

    ...actually be...
    INDEX(Risks!$E$2:$E$2460


    A B C D E F G H I
    1 Hospital Risk Risk Score St Elsewhere 8
    2 St Elsewhere Poisoning from Hospital meal 18 Risk 8 20
    3 St Elsewhere Surgeon can't tell Left from Right 6 Poisoning from Hospital meal 18
    4 St Elsewhere Stub toe on bed 11 Hospital Bill gives heart attack 18
    5 St Elsewhere Hospital Bill gives heart attack 18 Risk 6 17
    6 St Elsewhere Risk 5 5 Risk 7 17
    7 St Elsewhere Risk 6 17 Risk 24 17
    8 St Elsewhere Risk 7 17 Risk 26 17
    9 St Elsewhere Risk 8 20 Risk 27 17
    10 St Elsewhere Risk 9 10
    11 St Elsewhere Risk 10 6
    Risks


    Array Formulas
    Cell Formula
    I1 {=SUM(IF(Risks!$A2:$A2460=$H$1,IF(Risks!$F$2:$F$2460>=LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),MIN(5,COUNT(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460)))),1)))}
    H2 {=IF($I2="","",INDEX(Risks!$E$2:$E$2460,SMALL(IF(Risks!$A$2:$A$2460=$H$1,IF(Risks!$F$2:$F$2460=$I2,ROW(Risks!$A$2:$A$2460)-ROW($A$2)+1)),COUNTIFS($I$2:I2,I2))))}
    I11 {=IF(ROWS($I$2:I11)>$I$1,"",LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),ROWS($I$2:I11)))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


    Personally I'd use AGGREGATE to avoid the array formulae but you're so close it's probably not worth the surgery.

    Regards,
    Toadstool

  3. #3
    New Member
    Join Date
    Nov 2013
    Location
    Manchester
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to find highest unique values in a range

    Thanks for this Toadstool, works perfectly now!

    Appreciate your help and nice to know that I wasn't too far away from what I wanted to achieve!

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    230
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to find highest unique values in a range

    You're welcome!

    ...and I trust my test data for column E risk descriptions is nowhere near the actual data

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •