+ Reply to Thread
Results 1 to 4 of 4

Problems with custom sort order

  1. #1
    Registered User
    Join Date
    12-10-2008
    Location
    Aberdeen
    MS-Off Ver
    MS365 VER 2308
    Posts
    79

    Problems with custom sort order

    I have a list of people as follows:

    Col A - ID (starting at zero)
    Col B - Name
    Col C - Score (out of twenty)
    Col D - comments and details

    The list can vary in length - maybe sometimes with ten people, and other times with fifty.

    As imported, the list comes from the Windows File System (ie a file for each person) and as such is sorted alphabetically (one file > one row (persons name is filename)).

    I need to sort the rows so that they are in a random order, apart from the three highest scores, which should be in ascending order at the END of the list. Perhaps an example will help.

    john 5
    mary 18
    bill 12
    dave 10
    ricky 15
    jemima 18
    ian 17
    andy 8
    brian 20

    This should be sorted as follows:


    dave 10 <<<<< random order
    john 5 <<<<< random order
    ricky 15 <<<<< random order
    andy 8 <<<<< random order
    bill 12 <<<<< random order
    ian 17 <<<<< the third highest score
    mary 18 <<<<< the second highest score
    jemima 18 <<<<< also the second highest score
    brian 20 <<<<< the highest score

    note that there might be more 17's which would have to be sorted alongside ians score or more 20's which wouldbe sorted alongside brians score. If a 19 came along, then the 17's would be randomized among the "lower ranks"

    I tried using the "large" worksheet function, but although the documentation says that large(x,3) will find the third highest number, it doesn't. If the scores are 20,20,20,19,18,17, then Large(x,3) will give me 20, even though the third highest number is 18.

    thanks
    Guy

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Guy

    Try this. You will need to have column C available as a working column.

    rylo

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-10-2008
    Location
    Aberdeen
    MS-Off Ver
    MS365 VER 2308
    Posts
    79
    Thanks, but I think the "Loop Until nodupes.count = 3" part means that anything below the top three entries is randomized.

    I might need the top six to stay in place, if there are two scores of 20, two scores of 19 and two scores of 18, and only the rest should be randomized.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Did you actually run the code? If so and it wasn't giving you your required output, then can you please attach an example file and explain what it is doing, and what it should be doing.


    rylo

+ 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