Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ranking (Excel 97)

    I have a list of items that I have ranked using RANK(), and I manually sort them. Is there a way or is there source code available that would sort the ranked items automatically when a rank value is applied?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Ranking (Excel 97)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Casaler

    I am sorry but I don't understand your question:

    You said:

    "I have a list of items that I have ranked using RANK()" OK so now you have a list of numbers from 1 to x

    Then you said:

    "and I manually sort them" <img src=/S/confused.gif border=0 alt=confused width=15 height=20> What is them, the ranks() results or the "I have a list of items"

    Then you say:

    "Is there a way or is there source code available that would sort the ranked items automatically when a rank value is applied? "

    Sure, you can record your VBA code as you are sorting what you sort and then clean up the code. When you see the VBA code, you may understand how things are working and thus will be able to tailor the code to situations you know best about, or you can send another message asking for clarifications.

    I'll be on the lookout <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15> for your messages.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking (Excel 97)

    My apologizes for the confusion. My list is a set of priorities (54) that must be ranked for budget allocation for the upcoming fiscal year. One being the item with the highest priority. The list will be presented to a group of 10 people, including myself, who must rank the items listed from 1 to 54. I currently use the RANK() function, and sort the items using the A -Z shortcut on the menu/toolbar. I am looking for a way to avoid my current sorting method every time a rank value is added or changed. I would like to just enter or change the rank value, and have the ranking change automatically.

  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: Ranking (Excel 97)

    If I understand correctly, here is something I have done in the past, that might help:

    We had a priority list we used that had 5 "categories" we used to prioritize. Each one could be ranked 1-5 (5 being the highest priority).

    What we did was calculate an overall priority by multiplying them together and then adding the rownumber (in case of ties, first in list won). This was on one sheet.

    On the second sheet was the sorted list. This used the LARGE function to get the largest values (priorities)in descending order. Using MATCH with this priority and the value in the other sheet, gives the row number of that item. INDEX can be used with the list and the row number to list the Items in the second sheet sorted by priorities.

    Since it was all formulas (no macros) the sorted list was "live" any changes made to any of the values in the categories would resort the 2nd list and always keep it in priority order.

    HTH,
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking (Excel 97)

    In A1:A54, list the priorities. In B1:B54, input peoples' rankings. In D154, list the numbers 1 through 54. In E1, type =index($A$1:$A$54,match(D1,$B$1:$B$54,0)) Then copy this formula all the way down to E54.

    Column D and E will be the list you want.

    [edit] To help with possible duplicate entries in column B, you could type in F1 =countif($B$1:$B$54,d1) and copy this down the column. This will tell you how many times each ranking has been used.[/edit]

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ranking (Excel 97)

    Thanks, it worked out fine.

Posting Permissions

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