Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have the need to sort text code-values within same Excel cell | Excel Worksheet Functions | |||
Formula and values stay with row/cell after sort | Excel Worksheet Functions | |||
How do i sort values like: #103 / 4 / 23, need to sort by 2nd # | Excel Worksheet Functions | |||
Can I sort values only & leave orginal cell formatting? | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |