Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to get Top 3 with ties (Excel xp)

    I have a range of numbers in A6:N30. I only need the top 3 highest numbers in each column along with the description in COL A.

    The problem is how to deal with ties. If the numbers are 10,5,10,10,9,7 -- i only want to return 10,10,10, not the 9 or the 7 as i only want a maximum of 3 numbers (unless there is a 4 or 5 way tie for first).

    If there is a tie for 2nd, like 20,19,4,3,19,19,18 -- i want to return the 20,19,19,19 even tho there are 4 numbers

    If there is a tie for 3rd, like 14,7,6,6,13,7,11,9,11 -- i want to return the 14, 13, 11, 11

    I want to make a macro to sort the range by each column and return the top 3 plus whatever is in COL A. See the attached sample

    I also need to number them with 1), 2), 3). Thank you for the help.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: How to get Top 3 with ties (Excel xp)

    You're not one of those gymnastics judges at the Olympics, are you? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Your question reminds me, somewhat vaguely, of my question in <post#=382386>post 382386</post#>. I don't know if the suggestions I got in that thread would be helpful, as they were focused on worksheet functions and not VBA.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: How to get Top 3 with ties (Excel xp)

    Hi jha,

    Is there a particular reason that this needs a macro solution? See attached formula based solution for 'North'

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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