Rank by Wins, Alphabetically & Concatenate Results

  • I have looked at many Ranking Q & A’s in the forum but can’t find a formula which quite answers what I am looking for. I have a results grid and wish to Rank Names by most wins using the following criteria with a helper column:


    1. Most wins
    2. If equal on Wins (least number of Losses but with equal rank number)
    3. If still equal, alphabetically - A to Z


    Note that even although Fred has more losses and is placed behind Chas & Dave he is still equal on position 2 and lastly the W/ L Results are concatenated


    Thanks for looking and I hope I have explained it a satisfactorily.

  • Re: Rank by Wins, Alphabetically & Concatenate Results


    Try these:


    in H2:


    =LARGE($C$2:$C$6,ROWS(H$2:H2))&"-"&SMALL($D$2:$D$6,ROWS(H$2:H2))


    copied down


    In I2, we need a helper column (can be hidden):


    =VLOOKUP(G2,$B$2:$C$6,2,0)


    copied down


    in G2:


    =INDEX($B$2:$B$6,SMALL(IF($C$2:$C$6&"-"&$D$2:$D$6=H2,ROW($B$2:$B$6)-ROW($B$2)+1),COUNTIF(H$2:H2,H2)))


    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.


    in F2:


    =RANK(I2,I$2:I$6)


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Rank by Wins, Alphabetically & Concatenate Results


    Many thanks for your very prompt respone and although it works very well indeed it is not quite correct in that it doesn't seem to rank tied teams alphabetically as Dave should be listed above Fred although correctly shown with the same number under POS and I attach a copy to show you.


    Also I should have said that sometimes our competitions vary in the number of teams taking part but it won't allow me to leave any "blank" cells in Col B, maybe this is because of the array formula. Also this area is a small part of a much larger application that officials use for scoring and again is it possible that by the of use say IF??="","", or iserror etc. so that where cells have no data in them they stay blank and that the #Num! does not appear. It's really for cosmetic purposes but it would make the app look "neater" if you see what I mean if it is possible.


    But it's terrific what you have done and I really appreciate your help. Much Regards

  • Re: Rank by Wins, Alphabetically & Concatenate Results


    Hi weebilko,


    It just dawned on me that I didn't respond to your follow-up. I apologize for that. I had noticed it on the weekend, but by the time Monday had come, I had forgotten.... :(


    Anyway, I think we will need a couple more helper columns to also sort the results.... please see attached.


    To get rid of the errors, simply use the IFERROR() function wrapped around your formulas.


    So now we start with formula in H2:


    =IFERROR(LARGE($C$2:$C$6,ROWS(H$2:H2))&"-"&SMALL($D$2:$D$6,ROWS(H$2:H2)),"")


    then the helper columns:


    In I2:


    =IFERROR(INDEX($B$2:$B$6,SMALL(IF($C$2:$C$6&"-"&$D$2:$D$6=H2,ROW($B$2:$B$6)-ROW($B$2)+1),COUNTIF(H$2:H2,H2))),"") confirmed with CTRL+SHIFT+ENTER


    in J2:


    =IF(I2="","",COUNTIFS($H$2:$H$6,H2,$I$2:$I$6,"<"&I2))


    in K2:


    =IFERROR(VLOOKUP(I2,$B$2:$C$6,2,0),"")


    back to final formulas:


    in F2:


    =IFERROR(RANK(K2,K$2:K$6),"")


    in G2:


    =IFERROR(INDEX($I$2:$I$6,MATCH(1,($H$2:$H$6=H2)*($J$2:$J$6=SMALL(IF($H$2:$H$6=H2,$J$2:$J$6),COUNTIF(H$2:H2,H2))),0)),"") confirmed with CTRL+SHIFT+ENTER



    Copy these cells down.

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    Many many thanks, terrific, that seems perfect I am so glad you came back to me so please no apologies it is thanks to you folks that give so much of your time to help us fix our problems

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    No problem... and thanks for the positive feedback :)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    I think we will need to re-tool a couple of the formulas....


    First, helper 3 in BW61, change it to:


    =IFERROR(LARGE($BN$61:$BN$72,ROWS($BW$61:$BW61)),"")

    copied down.


    Then change BT61 to:


    =IFERROR(BW61&"-"&SMALL(IF($BN$61:$BN$72=BW61,$BO$61:$BO$72),COUNTIF($BW$61:$BW61,$BW61)),"")


    this one needs CTRL+SHIFT+ENTER confirmation, then copied down.


    The remainder of the formulas should be ok as they are.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    Try:


    =INDEX($BL$61:$BL$72,MATCH(BS61,$BM$61:$BM$72,0))


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    Try:


    =IF(BS61="","",INDEX($BL$61:$BL$72,MATCH(BS61,$BM$61:$BM$72,0)))


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    Are we talking about column BX? What exactly is the problem you are talking about?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Rank by Wins, Alphabetically &amp; Concatenate Results


    If your are talking about column BT, then change formula again too:


    =IFERROR(BW61&"-"&SMALL(IF($BN$61:$BN$72=BW61,IF($BO$61:$BO$72<>"",$BO$61:$BO$72)),COUNTIF($BW$61:$BW61,$BW61)),"")


    CSE confirmed and copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!