Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: 5 highest values with multiple criterias and possible tie

  1. #1
    New Member
    Join Date
    May 2018
    Location
    Finland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 5 highest values with multiple criterias and possible tie

    Hi, looked for forum to find answer for my question but didn't manage to find solution. I would like to find 5 top values from data, based on certain criteria. I also need way to present results, if there is a tie between 2 values, when status is same for both (for example companies: Foxtrot and India).
    I need to export the data from excel to some other softwares, so visual sorting isnt option. I think that the functions for: F3 and G3 are the most important (that's why i blurred the lats 3 columns of this example).

    I have tried the LARGE and SMALL functions, but my brains just explodes...

    Here is an example to descibre my problem in a case that Deal status is O:
    A B C D E F G H I
    J
    K
    1 Value Company Status Deals Open Deals Lost


    2 123 456 Alfa Lost No: Value Company No:
    Value
    Company
    3 11 000 Beta Won 1. 1


    4 12 500 Charlie Open 2. 2.


    5 123 456 Delta Lost 3. 3.


    6 200 050 Foxtrot Open 4. 4.


    7 290 321 Golf Open 5. 5.


    8 123 543 Hotel Won


    9 200 050 India Open


    10 125 076 Juliett Won




  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,200
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Hi and welcome to MrExcel,

    Somthing like this:

    ABCDEFGHIJK
    1ValueCompanyStatusDeals OpenDeals Lost
    2123456AlfaLostNo:ValueCompanyNo:ValueCompany
    311000BetaWon1290321Golf1123456Alfa
    412500CharlieOpen2200050Foxtrot2123456Delta
    5123456DeltaLost3200050India3
    6200050FoxtrotOpen412500Charlie4
    7290321GolfOpen55
    8123543HotelWon
    9200050IndiaOpen
    10125076JuliettWon

    Sheet1



    Array Formulas
    CellFormula
    F3{=IFERROR(LARGE(IF($C$2:$C$10="Open",$A$2:$A$10,""),E3),"")}
    G3{=IFERROR(INDEX($B$2:$B$10,SMALL(IF($F3=$A$2:$A$10,ROW($A$2:$A$10)-ROW($A$2)+1),IF(COUNTIFS($A$2:$A$10,F3,$C$2:$C$10,"open")>1,ROW()-1-COUNTIFS($A$2:$A$10,F3,$C$2:$C$10,"open"),COUNTIFS($A$2:$A$10,F3,$C$2:$C$10,"open")))),"")}
    J3{=IFERROR(LARGE(IF($C$2:$C$10="Lost",$A$2:$A$10,""),I3),"")}
    K3{=IFERROR(INDEX($B$2:$B$10,SMALL(IF($J3=$A$2:$A$10,ROW($A$2:$A$10)-ROW($A$2)+1),IF(COUNTIFS($A$2:$A$10,J3,$C$2:$C$10,"lost")>1,ROW()-COUNTIFS($A$2:$A$10,J3,$C$2:$C$10,"lost"),COUNTIFS($A$2:$A$10,J3,$C$2:$C$10,"lost")))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

  3. #3
    New Member
    Join Date
    May 2018
    Location
    Finland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Thank you for this help, I tried to modify the code and needed to change , to ;
    Then I tried to change that specified data range fromA2:A10 to column A, and the functions got broken. I recieved random numbers from the range and with random Company names . For example: $A$2:$A$10 I replaced with $A:$A. Is that wrong way determining data range, or did i possibly change something else to get random information ?


  4. #4
    New Member
    Join Date
    May 2018
    Location
    Finland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Quote Originally Posted by wixie View Post
    Thank you for this help, I tried to modify the code and needed to change , to ;
    Then I tried to change that specified data range fromA2:A10 to column A, and the functions got broken. I recieved random numbers from the range and with random Company names . For example: $A$2:$A$10 I replaced with $A:$A. Is that wrong way determining data range, or did i possibly change something else to get random information ?

    Hmm i managed to notice, that if I change the values of ranged data, function breaks and I get some random company names...

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,200
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Hi,

    With regards to the comma versus semicolon: The standard separator within an excel function is set by the list separator within the regional settings of windows.
    As the comma within the settings for Finland is used as the decimal symbol, windows will set the semicolon as list separator.
    My version is set to English hence the use of the comma.

    With regards to the complete column: As these are Array functions you can't just change the range to a complete column. A complete column, however convenient in some cases, will, in most cases, have empty cells within. A empty cell does "break" the result of the formula so don't use complete columns within this function.
    Don't forget: these are array functions which should be confirmed with (CTRL+Shift+Enter)
    Last edited by jorismoerings; May 24th, 2018 at 02:28 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

  6. #6
    New Member
    Join Date
    May 2018
    Location
    Finland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Thank you for your answers, I appreciate help very much.

    Alrighty, is there anyway to "handle" the empty cells by skipping them or something? The thing is that the data that I'll use for the functions, will be exported from other system ('ll save them as excel workbook), then I will run the the functions in separate excel work book. I can't determine the exact amount of rows because data amount varies every time.

  7. #7
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,200
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Hi,

    Not the cleanest solution and this will absolutely have impact on calculation time if your dataset is huge but try this:

    ABCDEFGHIJK
    1ValueCompanyStatusDeals OpenDeals Lost
    2123456AlfaLostNo:ValueCompanyNo:ValueCompany
    311000BetaWon1290321Foxtrot1123456Alfa
    412500CharlieOpen2290321Golf2123456Delta
    5123456DeltaLost3200050India3
    6290321FoxtrotOpen412500Charlie4
    7290321GolfOpen55
    8123543HotelWon
    9200050IndiaOpen
    10125076JuliettWon

    Sheet3



    Array Formulas
    CellFormula
    F3{=IFERROR(LARGE(IF($C:$C=RIGHT(E$1,4),$A:$A,""),E3),"")}
    G3{=IFERROR(INDEX($B:$B,SMALL(IF(F3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))>1,ROW()-COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4)),COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))))),"")}
    J3{=IFERROR(LARGE(IF($C:$C=RIGHT(I$1,4),$A:$A,""),I3),"")}
    K3{=IFERROR(INDEX($B:$B,SMALL(IF(J3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))>1,ROW()-COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4)),COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,056
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    ABCDEFGHIJK
    1ValueCompanyStatusDeals Open54Deals Lost52
    2123456AlfaLostNo:ValueCompanyNo:ValueCompany
    311000BetaWon1290321Golf1123456Alfa
    412500CharlieOpen2200050Foxtrot2123456Delta
    5123456DeltaLost3200050India
    6200050FoxtrotOpen412500Charlie
    7290321GolfOpen
    8123543HotelWon
    9200050IndiaOpen
    10125076JuliettWon

    Sheet1





    In G1 just enter:

    =MIN(COUNTIFS(C2:C10,"open"),F1)

    In E3 just enter and copy down:

    =IF($F3="","",ROWS($F$3:F3))

    In F3 control+shift+enter, not just enter, and copy down:

    =IF(ROWS($F$3:F3)>$G$1,"",LARGE(IF($C$2:$C$10="open",$A$2:$A$10),ROWS($F$3:F3)))

    In G3 control+shift+enter, not just enter, and copy down:

    =IF($F3="","",INDEX($B$2:$B$10,SMALL(IF($C$2:$C$10="open",IF($A$2:$A$10=$F3,ROW($B$2:$B$10)-ROW($B$2)+1)),COUNTIFS($F$3:F3,F3))))

    In K1 just enter:

    =MIN(COUNTIFS(C2:C10,"lost"),F1)

    In I3 just enter and copy down:

    =IF($J3="","",ROWS($J$3:J3))


    In J3 control+shift+enter, not just enter, and copy down:

    =IF(ROWS($J$3:J3)>$K$1,"",LARGE(IF($C$2:$C$10="lost",$A$2:$A$10),ROWS($J$3:J3)))

    In K3 control+shift+enter, not just enter, and copy down:

    =IF($J3="","",INDEX($B$2:$B$10,SMALL(IF($C$2:$C$10="lost",IF($A$2:$A$10=$J3,ROW($B$2:$B$10)-ROW($B$2)+1)),COUNTIFS($J$3:J3,J3))))
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    May 2018
    Location
    Finland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Hmm, i seem to get NAME! -error and started wondering if I have typo somewhere. What does the number 4 mean in RIGHT - formula?

  10. #10
    New Member
    Join Date
    May 2018
    Location
    Finland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 5 highest values with multiple criterias and possible tie

    Quote Originally Posted by jorismoerings View Post
    Hi,

    Not the cleanest solution and this will absolutely have impact on calculation time if your dataset is huge but try this:

    A B C D E F G H I J K
    1 Value Company Status Deals Open Deals Lost
    2 123456 Alfa Lost No: Value Company No: Value Company
    3 11000 Beta Won 1 290321 Foxtrot 1 123456 Alfa
    4 12500 Charlie Open 2 290321 Golf 2 123456 Delta
    5 123456 Delta Lost 3 200050 India 3
    6 290321 Foxtrot Open 4 12500 Charlie 4
    7 290321 Golf Open 5 5
    8 123543 Hotel Won
    9 200050 India Open
    10 125076 Juliett Won
    Sheet3

    Array Formulas
    Cell Formula
    F3 {=IFERROR(LARGE(IF($C:$C=RIGHT(E$1,4),$A:$A,""),E3),"")}
    G3 {=IFERROR(INDEX($B:$B,SMALL(IF(F3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))>1,ROW()-COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4)),COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))))),"")}
    J3 {=IFERROR(LARGE(IF($C:$C=RIGHT(I$1,4),$A:$A,""),I3),"")}
    K3 {=IFERROR(INDEX($B:$B,SMALL(IF(J3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))>1,ROW()-COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4)),COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Hmm can't edit my posts, i meant this solution in my recent question.

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
  •