Sorting problem

[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

 

[Bernie Deitrick]

Patrick,

You could use another block of formulas, which wouldn't affect the original
entries, but could be used in their place. Enter this into cell G1, then
copy to G1:K5.

=SMALL($A$1:$E$5,(ROW()-ROW($G$1))*5+COLUMN()-COLUMN($G$1)+1)

Or you could use a macro: see macro code below.

HTH,
Bernie
MS Excel MVP

Sub BlockSort()
Dim i As Integer

Range("A1").EntireColumn.Insert

For i = 2 To 6
Cells(1, i).Resize(5, 1).Copy _
Range("A65536").End(xlUp)(2)
Next i

Range(Range("A2"), Range("A2").End(xlDown)).Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo

For i = 2 To 6
Cells((i - 2) * 5 + 2, 1).Resize(5, 1).Copy
Range("B" & (i - 1)).PasteSpecial Transpose:=True
Next i

Range("A1").EntireColumn.Delete

End Sub