# Thread: Sort numbers across multiple columns! (Excel 2000>)

1. ## Sort numbers across multiple columns! (Excel 2000>)

Is this at all possible. To sort numbers ascending across multiple columns as in the sample file?

2. ## Re: Sort numbers across multiple columns! (Excel 2000>)

You can try this formula in I2:N20:

=SMALL(\$B\$2:\$G\$20,ROW()+19*(COLUMN()-9)-1)

19 is the number of rows in the range B2:G20, and 9 is the column number of the first column in the target range (column I)

3. ## Re: Sort numbers across multiple columns! (Excel 2000>)

Well blow me away! Here I'm becoming boxing in with thinking LBound/Ubound Arrays, and you come with a interesting formula! Cheers Hans... you have officially <img src=/S/drop.gif border=0 alt=drop width=23 height=23> me today!

PS: Now what formula can you construct to make Excel wash the dishes for me????

4. ## Re: Sort numbers across multiple columns! (Excel 2000>)

<pre>Option Explicit

Public Function Wash(Dishes As Range)
Dishes.Clear
End Function
</pre>

Name the range to be washed Dishes and:

<pre>=Wash(Dishes)
</pre>

<img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Yes, I know it will get an error.

5. ## Re: Sort numbers across multiple columns! (Excel 2000>)

Here is an approach that just sorts the list. It works with values other than numbers as well. It takes the list, makes 1 big list, sorts it (in a temp wks), then replaces it in the original spot.

Steve

<pre>Option Explicit
Sub SortMultColList()
Dim wks As Worksheet
Dim rng As Range
Dim rSource As Range
Dim vList() As Variant
Dim vList1 As Variant
Dim vList2 As Variant
Dim lRow As Long
Dim iCol As Integer
Dim x As Long
Dim lRows As Long
Dim iCols As Integer
Dim lCount As Long
Set rSource = Selection
vList2 = rSource.Value
lRows = UBound(vList2, 1)
iCols = UBound(vList2, 2)
lCount = lRows * iCols
If lCount > 65536 Then
MsgBox "List too big"
Exit Sub
End If
ReDim vList(1 To lCount, 0)
x = 1
For iCol = 1 To iCols
For lRow = 1 To lRows
vList(x, 0) = vList2(lRow, iCol)
x = x + 1
Next
Next
With wks
Set rng = .Range(.Range("a1"), .Cells(lCount, 1))
rng = vList
rng.Sort .Range("a1"), xlAscending
End With
vList1 = rng.Value
x = 1
For iCol = 1 To iCols
For lRow = 1 To lRows
vList2(lRow, iCol) = vList1(x, 1)
x = x + 1
Next
Next
rSource = vList2
wks.Delete

Set rng = Nothing
Set rSource = Nothing
Set wks = Nothing</pre>

End Sub

6. ## Re: Sort numbers across multiple columns! (Excel 2

<img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

7. ## Re: Sort numbers across multiple columns! (Excel 2000>)

<img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Legare... If only it were that easy!!!

Steve, what else could I expect than to get a answer via code...you've done it so many times in the past for me!
I will study this up during the week and see what it does.