Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    here's a challenge! (Excel 2000, SR-1a)

    I hope someone here can help me with some programming. I will be running a bowling tournament which will be offering several qualifying shifts before selecting the top scores for the finals. Any individual bowler will be able to enter every qualifying shift if they wish, with only their best score being used for the finals qualifier. What I want to do is create a macro in excel which will compile all entries, then create a list of participants in order of qualification, from highest score to lowest, using only one score (their best score) for each competitor. In other words, let's say Bob bowled in four qualifying shifts, recording scores of 1620, 1597, 1523, and 1688. Bob's name is to appear on the final list only once, using his high score of 1688.
    I could manually do a sort, first by name, with a secondary sort by score, then delete the lower scores for each individual, then do another sort by scores for positioning purposes to produce my final list, but there could be hundreds of entries making it rather time consuming. There must be a more elegant way of doing this, using VBA for Excel. How about it, folks! Any help is much appreciated! :-)

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: here's a challenge! (Excel 2000, SR-1a)

    If you have your names in column A and scores in column B:
    Somewhere below the range filled with names, enter all of the unique bowler names (this will be easiest by selecting the range with the names in it, then clicking Data | Filter | Advanced Filter; click the checkmark for 'unique entries only' and the radio button for 'copy to another location' and give it a location below the actual data range)

    In column B you will have to enter an array formula to grab the highest score for each bowler. Assuming that the data runs from A2:B23, and the list of unique names begins in row 25, you would type (or click-and-drag to create) the following formula:
    <pre>=MAX(IF($A$2:$A$23 = A25,$B$2:$B$23,0))
    </pre>

    to enter an array formula you have to press ctrl-shift-enter and Excel will place a curly bracket around the formula to indicate that it is an array formula. Drag to copy that formula down to fill the range beside all the unique names. The result will be a list of names and their highest scores. You can select that range and sort it in descending order by the value in column B.

    HTH

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Thanks Dean! I see how this can work - and a lot less work for me. I have learned much from your posting, and I see you are a fellow Canadian! (I'm from Saskatoon, Sask). I will give this a test run today. I assume by that changing the cell numbers appropriately, I can have the sorted list appear at the top of the page rather than below the initial entries. Please stay tuned - I will be having an even more complicated event to work through, pairing multiple doubles scores. That will be fun...
    Thanks again!
    - Rod

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Here is a way using some formulas. I just made up some names and scores for demo.

    Steve
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Dean - this formula has a problem - the final results post the high score of the first competitor into every competitor's results, resulting in everyone having the same score!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Have you thought about doing this in Access?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Steve - thanks for the worksheet. I am trying to work my way through it right now, but am running into some problems with names disappearing and being replaced with #NUM! in columns G and H when I add new names and scores to the list in columns A and B (I did expand the formulae along with the additions), and those new names and scores don't show up in G and H as they should. Changing the sort of the names changes the columns as well. This probably can be made to work, with a few changes here and there - thanks again for your help.
    - Rod

  8. #8
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    I would love to do this in Access, Pat, except I don't have it on my home computer, and probably never will. ($$$!)

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Don't you have Office 2000 on your home PC?
    Doing this in Access would be a lot easier, but then I'm probably stating the obvious.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: here's a challenge! (Excel 2000, SR-1a)

    Sorry to be so long getting back to you - I suspect that you have an extra "$" (or two) in the array formula, so that you are always testing the same value. I have attached a s/sheet with some dummied up values so you can check it against yours....
    Greetings from the (normally) wet coast <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Attached Files Attached Files

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    1) make sure you expand the ranges named names and scores
    2) expand the column of numbers 1,2,3 ... to the entire length of the dataset
    3) copy all the other columns down to the end of the data set.
    4) the "added rows" don't need to be sorted, they will sort automatically in the final columns. (you could use a conditional format to "hide" the #nums, or change the formula to eliminate.)

    Steve

  12. #12
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Thanks again, Dean. This works perfectly. I was making an error in the array by not comparing to the appropriate name cell in column A (duh!?!) Your sample makes it perfectly clear.
    How about sending some of that warm Pacific air this way - it's -21 here right now...
    - Rod

  13. #13
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Steve - thanks again for your reply. I will be studying your sheet as I can learn much from it. It presents a lot of information, but also is much more complicated than the example Dean produced. Both are useful. Thanks to your (and Dean's) help, I ended up producing a solution of my own that works very well. By listing the names in column A, then the qualifying shift scores in columns B,C,D, and E, then using the formula =max(B2:E2) in column F for row 2, and appropriate changes for the remaining rows, I get a nice tidy list that is easy to sort when either interim or final results are desired. Plus, it saves typing in the same name over again, and helps prevent errors/confusion if the name is typed in wrong the second time. I'm attaching the file, if you are interested...
    p.s. - for Pat - I have an Office 2000 edition that does not include Access. It is provided by arrangement through my employer. For some reason, they give me Access at work (and I use it there), but not for home use. Go figure...
    Rod
    Attached Files Attached Files

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Whatever works best for you.

    The advantage to my way is that it does NOT require ANY extracting of unique names nor does involve any sorting of the dataset NOR does it use a macro. (though I do understand that the formulas are a lot more complicated)

    This technique will extract the sorted list of only unique names.

    Steve

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: here's a challenge! (Excel 2000, SR-1a)

    Hi Rod

    Ours is not to reason why, huh <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Glad you got it sorted out.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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