+ Reply to Thread
Results 1 to 8 of 8

Recognizing two of the same number in a row

  1. #1
    Registered User
    Join Date
    10-23-2009
    Location
    Sarasota, Florida
    MS-Off Ver
    Excel 2007
    Posts
    30

    Recognizing two of the same number in a row

    I have a football pool worksheet.
    My goal is to have a button that displays a message box with the players in order of how many points they have.
    So far I have been able to display this message box with one problem...

    The problem occurs when there is a tie between two players. If they have they same score it will only display the first score in the row.
    In my attachments when you press the macro button "score" it displays a message box saying:
    "Sue is in first place with 12 points.
    Bob is in second place with 9 points.
    Bob is in third place with 9 points.
    Larry is in forth place with 3 points."

    I want it to say:
    "Sue is in first place with 12 points.
    Bob and Dave are tied for second with 9 points.
    Larry is in (third or forth, doesn't matter) place with 3 points."

    Even if it needs to say
    "Bob is in second place with 9 points.
    Dave is in third place with 9 points."
    would be an improvement.

    Here is my code so far and i've attached the file.

    Please Login or Register  to view this content.
    Thank you.
    Attached Files Attached Files
    Last edited by CrystalNewb; 12-10-2009 at 07:54 AM.

  2. #2
    Registered User
    Join Date
    10-23-2009
    Location
    Sarasota, Florida
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Recognizing two of the same number in a row

    Bump for help

  3. #3
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Recognizing two of the same number in a row

    I see it says you have excel 2003. Could you post an example in 2003? I can't open the file you have attached, but I'm thinking there is a much easier and more succinct way to do this.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  4. #4
    Registered User
    Join Date
    10-23-2009
    Location
    Sarasota, Florida
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Recognizing two of the same number in a row

    Here is an example saved in excel 2003

    by the way... how do i change my profile to say I now have 2007?
    Attached Files Attached Files

  5. #5
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Recognizing two of the same number in a row

    I suggest setting up your spreadsheet differently, with the grandtotal positioned in the same place at the top of the spreadsheet. Then you can use the cells position instead of referencing ranges.

    That being said, all you need to do is look into the rank function. It will give you the place they are in without manipulating a number of other functions. If you are desiring a message box, build in an if clause that says if two ranks are the same, msgbox says ... are tied for nth place.

    Does that make sense, or do you need more details?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Recognizing two of the same number in a row

    Quote Originally Posted by CrystalNewb View Post
    by the way... how do i change my profile to say I now have 2007?
    Click on User CP at the top left of the menu bar. Then click on Edit your Details.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  7. #7
    Registered User
    Join Date
    10-23-2009
    Location
    Sarasota, Florida
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Recognizing two of the same number in a row

    Thank you, it definitely looks like this RANK function is the direction i need to go in.

    I'm afraid I will need some more details on how to use it to do what I want, where i get a message box displaying first place and so on.

    sorry for the inconvenience but thank you for the help!

  8. #8
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Recognizing two of the same number in a row

    Hi

    This was actually harder to do than I had originally anticipated. Here is what I've come up with. You'll probably have to modify this to meet your purpose, but it should show you one way that works.
    Attached Files Attached Files

+ 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