+ Reply to Thread
Results 1 to 8 of 8

return second name in event of tie

  1. #1
    Registered User
    Join Date
    10-17-2010
    Location
    vancouver
    MS-Off Ver
    Excel 2007
    Posts
    3

    return second name in event of tie

    I am hoping someone can help me solve the problem of how to input the name of a player who is tied for first place. Excel will input only the first name of the high score, yet cannot do so for the other player with the same score.

    In A4:A14, I have a list of players. In D4:D14, I have the score for each player.
    In D16, I have the top score, using the formula: =LARGE(D4:D13,1).
    In cell C16, I have matched the score to the players name using the following formula:
    =INDEX(A4:A14,MATCH(MAX(D16),D4:D13,0),1)
    In cell C18, I have matched the score to the second highest player's name:
    =INDEX(A4:A14,MATCH(MAX(D18),D4:D13,0),1)
    The same formula was used for the third place ranking.

    The problem I have encountered is in the case where there is a tie for first place. The first instance of the high score will return the corresponding name. However, I cannot return the name for the other player that shares the same high score.

    Does this make sense? I appreciate your help!
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: return second name in event of tie

    If we assume the names are unique then for one single formula common to all three name cells:

    Please Login or Register  to view this content.
    the above should accommodate ties or other
    Last edited by DonkeyOte; 10-18-2010 at 02:45 AM.

  3. #3
    Registered User
    Join Date
    10-17-2010
    Location
    vancouver
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: return second name in event of tie

    Thank you for your response DonkeyOte.
    I copied your formula into the three cells, however all names yielded "Player Six". Is there something I am doing wrong?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: return second name in event of tie

    When I said "copy to B17, B19" I meant first enter the formula provided to B15 then copy the formula from B15 to B17 to B19 respectively as opposed to copying the formula provided directly to B15,B17,B19.

    Above may sound a little confusing but in essence the relative references in the formula provided will update when you copy B15 to B17 etc...
    if you simply paste the above into each cell it will not work given all formulae are identical and all will return 1st match

  5. #5
    Registered User
    Join Date
    10-08-2010
    Location
    swansea wales
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: return second name in event of tie

    Why don't you break the first place tie by ordering on "Best scoring player" and "Most Goals". You could further break ties by including "Most Assists" and so on. You would need another column which provided a modified position number and then apply your existing code to the new column to obtain your result display.

    Michael Coltart

  6. #6
    Registered User
    Join Date
    10-17-2010
    Location
    vancouver
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: return second name in event of tie

    Thanks Donkey Ote. That worked.

  7. #7
    Registered User
    Join Date
    06-19-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    9

    Unhappy Re: return second name in event of tie

    I have a similar problem to the original question but in my case it is likely for several teams to have the same scores. How do I list all the teams which have the same high score?

    Row 23 has the 'Stand' winners in i,t but how do I show an occasion such as column E where there are four teams with the same high score?

    I tried to adapt the solution supplied above but I got lost in what I was trying to do! Any help would be gratefully received.
    Attached Files Attached Files

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: return second name in event of tie

    Sandy,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1