Sort Solution needed

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

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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:ePzmcsdKFHA.2640@TK2MSFTNGP09.phx.gbl...
>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
>

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.

Patrick Simonds wrote:
>
> 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

--

Dave Peterson