rankings within spreadsheets

[johnboy61053]

I have a spreadsheet with multiple numerical columns. I need a way to automate it so each column will have its information ranked best to worst (1 to 30) without having to go through and sort and rank each one seperately.

[Al Chara]

Try the following:

Sub SortCols()
Dim cols As String, col As String
cols = InputBox("Enter the columns you want to sort without spaces")
For i = 1 To Len(cols)
col = Mid(cols, i, 1)
Columns(col).Sort Key1:=Range(col & "1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next
End Sub

[Yogi Anand]

On 2002-05-14 15:55, johnboy61053 wrote:
I have a spreadsheet with multiple numerical columns. I need a way to automate it so each column will have its information ranked best to worst (1 to 30) without having to go through and sort and rank each one seperately.

Let us say your entries are in cells A1:A30

then in B1 enter the following array formula

=LAREGE(A1:A30,ROW(1:30))

then highlite cells B1:B30 and array enter --that is key in CTRL+SHIFT+ENTER

Cells B1:B30 will have all the entries of cells A1:A30 arranged in descending order.

And now whenever a value changes in range A1:A30, B1:B30 descending order is dynamically adjusted.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!