Macro Challenge!

[posborne]

Anyone fancy a macro challenge?
I'm completely stumped!
I have up to 120 values. most are unique values, but not all.
I need to rank these in a particular order:
lowest value at position 120 (Cell A121 with the header)
2nd lowest value at position 110 (A111)
3rd lowest at 100 etc.
When all the '10's' are completed i need to go on to the '5's', so the
13th lowest value at position 115 (A116), 14th lowest at 105 etc.
When the 5's are completed i need to continue the same logic with the
cells filled in the following order;
*1, *6, *2, *7, *3, *8, *4, *9 so that the lowest value is in position
9, cell A10.
To make matters even more complicated i might not have a full 120
values. For example if i have 87 values then the lowest number needs to
be in position 80, then 70 etc down to 10, then to 85!
Anyone after the worldwide aclaim of acheiving this feat with a macro
(or otherwise)

 

[Peo Sjoblom]

Why would you need a macro for that? Look in help at the LARGE function


in A121

=MAX(B1:B120)

in A111

=LARGE(B1:B120,2)

A101

=LARGE(B1:B120,3)


Having said that, there is also a RANK function that can be used

 

[posborne]

Thanks for that - almost!
Sorry, forgot to mention i need to take text in column B with it, as
attached example!

Attachment filename: macro challenge sample data.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=404519

 

[posborne]

Also i don't think that the =LARGE function will work when i want t
sort data of less then 120 rows - i would have to re-do the rankin
number on the whole lot

 

[Peo Sjoblom]

You can use a dynamic range where it doesn't matter how many rows you have..

http://www.contextures.com/xlNames01.html#Dynamic

 

[posborne]

Thanks Peo
Your solutions have almost worked, but not quite the solution i need.
because the position i need the value ranked to is entirely dependan
on the number of records i have, and the result is obviously only show
in the cell where the formula is - i will always get gaps in the resul
column when there are less than 120 records.
If i have a full 120 records then the 12 highest values need to fil
the positions 120, 110, 100 etc, then the next 12 highest 115, 10
etc.
If i have 85 records then the 8 highest need to be lines 80, 70... an
the next 8 on lines 85, 75, 65...
Can i do this without a macro