Sorting problem

P

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
 
B

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
 
Top