Sort Solution needed

[Patrick Simonds]

I have a worksheet with 5 columns and 5 rows. A number gets entered into
each cell (A1:E5). I need to sort so that each number is placed in
sequential order (smallest to largest) across the columns and down the rows
(see example below). Can this be done?


1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

 

[Earl Kiosterud]

Patrick,

One way might be to put the numbers in a column, then make a table of links
to the 5x5 range you need. The column will sort easily. But you'll have
to enter the numbers into the column -- the table of links will only display
them in the 5x5 grid you want. If you need to actually put the numbers into
the 5x5 grid, it'll not work. That will probably require a macro. Post
back.

 

[Dave Peterson]

Since the number of cells is pretty small, you could just sort it in an array
and it shouldn't hurt too bad:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim jCtr As Long
Dim myArr As Variant
Dim Swap As Variant

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("a1:E5")
ReDim myArr(1 To myRng.Rows.Count * myRng.Columns.Count)
iCtr = 0
For Each myCell In myRng.Cells
iCtr = iCtr + 1
myArr(iCtr) = myCell.Value
Next myCell

For iCtr = LBound(myArr) To UBound(myArr) - 1
For jCtr = iCtr + 1 To UBound(myArr)
If myArr(iCtr) > myArr(jCtr) Then
Swap = myArr(iCtr)
myArr(iCtr) = myArr(jCtr)
myArr(jCtr) = Swap
End If
Next jCtr
Next iCtr

For iCtr = 1 To myRng.Rows.Count
For jCtr = 1 To myRng.Columns.Count
myRng(iCtr, jCtr).Value = myArr(iCtr * jCtr)
Next jCtr
Next iCtr

End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ps. If you're going to post this to multiple groups, please post it once, but
include all the newsgroups in the header--then every newsgroup will get each
reply.