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
Bookmarks