Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Top 10 list (97 SR-2)

    I have a list of data containing five columns. The first column is the date (there can be multiple dates resulting in an A, B or C after the date), the next four columns are scores for four different people (John, Paul, George or Ringo). This is an abstraction of a problem I am trying to solve. I want to create a list of the top ten highest scores and lowest scores. I can find the scores using the Large or Small function. The problem I am having is finding the associated date and person for the score. This is especially difficult when each person could have the same score in the top ten or one person could have two equal scores in the top ten. I have attached a sample file. I am adding data to the bottom of the file so I would like to avoid sorting it. I want the top ten to calculate automatically after adding new data. I attempted to solve the problem using the match and indirect functions. It gets complicated quickly!

    Thanks,
    Mark
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Top 10 list (97 SR-2)

    If you would have just a three column list with Dates, names and scores in each column you could use a pivot table. You can set it to sum per week, month, whatever and then show top 10 values.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Excel Top 10 list (97 SR-2)

    You're right, it does get extremely complicated to try and do what you want with formulas. Although I generally think that a VBA solution should be used as a last resort, in this case it's probably simpler. Attached basically takes your array of data, converts it to a 3-column list (on an inserted sheet), sorts the list, then copies the top 10 and bottom 10 rows to your 'Top 10' sheet. It then deletes the inserted sheet. Post back if you have questions or if it needs further refinement.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Excel Top 10 list (97 SR-2)

    P.S. Just noticed you're on Excel 97. There are a couple of lines of code that I'm not sure about in Excel 97. If you get an error, let me know and I'll make modifications.

  5. #5
    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: Excel Top 10 list (97 SR-2)

    The code:

    Range("A1").CurrentRegion.Sort, Header:=xlYes, _

    caused an error. It does NOT have a comma and should be this:
    Range("A1").CurrentRegion.Sort Header:=xlYes, _

    (at least in excel97)

    Everything else seemed to run okay.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Excel Top 10 list (97 SR-2)

    Interesting. That was a typo on my part, but evidently XL2000 ignores it, whereas XL97 doesn't.
    Thanks for catching that. I'm glad it works ok in '97 (once you fix my mistakes).

  7. #7
    New Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Top 10 list (97 SR-2)

    It looks like that will work. I was also looking for a non-VB solution. I thought there might be an easier way I was missing. I decided my method wasn't going to work after my formula exceeded the maximum allowable length. I also didn't want to have to use linked formulas in multiple cells.

    Thanks

  8. #8
    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: Excel Top 10 list (97 SR-2)

    Here is a "megaformula" solution, though I prefer the VB approach. the megaformulas are too large. Also you must make "intermediate calcs" to get the data "sorted". I used Value, person, date as my sort.

    Steve
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Top 10 list (97 SR-2)

    That was an interesting solution. I'll probably be incorporating it in my final solution.

    Thank,
    Mark

Posting Permissions

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