Sort cell values
 

Hi,

I want to sort a cell value like "6, 4, 5, 3, 1, 2"
to get the result "1, 2, 3, 4, 5, 6"
What ist the best way to do this?

TIA

Markus Serbel

Sort cell values
 

Markus,

The best way is to use a Custom User-Defined-Function. See the code
below. Can either be used in VBA or as a Worksheet function. If used
as a worksheet function, use it like:

=SortCell(A1, TRUE)

to return

1, 2, 3, 4, 5, 6

The macro TryIt shows how to use it in VBA - assumes the input string
is in cell A1.

Both require Excel2000 or later to function, due to the Split and Join
commands.

HTH,
Bernie
MS Excel MVP

Function SortCell(InCell As Range, myBool As Boolean) As Variant
SortCell = Join(ArrayBubbleSort(Split(InCell.Value, ", "), myBool), ",
")
End Function

Sub TryIt()
MsgBox Join(ArrayBubbleSort(Split(Range("A1").Value, ", "), True), ",
")
End Sub

Function ArrayBubbleSort( _
myArray As Variant, _
Optional Ascending As Boolean) _
As Variant
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

'Do the sort
For i = LBound(myArray) To UBound(myArray) - 1
For j = i + 1 To UBound(myArray)
If Ascending Then
If myArray(i) myArray(j) Then
myTemp = myArray(j)
myArray(j) = myArray(i)
myArray(i) = myTemp
End If
Else
If myArray(i) < myArray(j) Then
myTemp = myArray(j)
myArray(j) = myArray(i)
myArray(i) = myTemp
End If
End If
Next j
Next i

'Return the array
ArrayBubbleSort = myArray
End Function




"Markus Serbel" wrote in message
...
Hi,

I want to sort a cell value like "6, 4, 5, 3, 1, 2"
to get the result "1, 2, 3, 4, 5, 6"
What ist the best way to do this?

TIA

Markus Serbel

Sort cell values
 

Bernie Deitrick wrote:
The best way is to use a Custom User-Defined-Function. See the code
below. Can either be used in VBA or as a Worksheet function. If used
as a worksheet function, use it like:

Great, Bernie! Thank you very much.
But there's a little problem.
If there are a values like "1, 5, 10, 2, 19"
the result is "1, 10, 19, 2, 5"

Is there a solution to get "1, 2, 5, 10, 19" ?
(The highest Value will be 29)

Thanky again,
Makrus

Sort cell values
 

Markus,

Sorry about that. Use the code below, which forces the values to be
numeric.

HTH,
Bernie
MS Excel MVP

Function ArrayBubbleSort( _
myArray As Variant, _
Optional Ascending As Boolean) _
As Variant
Dim myTemp As Variant
Dim myInt() As Variant
Dim i As Integer
Dim j As Integer

ReDim myInt(LBound(myArray) To UBound(myArray))
For i = LBound(myArray) To UBound(myArray)
myInt(i) = Val(Trim(myArray(i)))
Next i

'Do the sort
For i = LBound(myInt) To UBound(myInt) - 1
For j = i + 1 To UBound(myInt)
If Ascending Then
If myInt(i) myInt(j) Then
myTemp = myInt(j)
myInt(j) = myInt(i)
myInt(i) = myTemp
End If
Else
If myInt(i) < myInt(j) Then
myTemp = myInt(j)
myInt(j) = myInt(i)
myInt(i) = myTemp
End If
End If
Next j
Next i

'Return the array
ArrayBubbleSort = myInt
End Function

Sub TryIt2()
MsgBox Join(ArrayBubbleSort(Split(Range("A1").Value, ", "), True), ",
")
End Sub

Function SortCell(InCell As Range, myBool As Boolean) As Variant
SortCell = Join(ArrayBubbleSort(Split(InCell.Value, ", "), myBool), ",
")
End Function


"Markus Serbel" wrote in message
...
Bernie Deitrick wrote:
The best way is to use a Custom User-Defined-Function. See the
code
below. Can either be used in VBA or as a Worksheet function. If
used
as a worksheet function, use it like:

Great, Bernie! Thank you very much.
But there's a little problem.
If there are a values like "1, 5, 10, 2, 19"
the result is "1, 10, 19, 2, 5"

Is there a solution to get "1, 2, 5, 10, 19" ?
(The highest Value will be 29)

Thanky again,
Makrus

Sort cell values
 

Bernie Deitrick wrote:
Sorry about that. Use the code below, which forces the values to be
numeric.

Perfect! Thanks again, Bernie.

Markus