Sorting numbers in a row from small to large

[Peter Herman]

Hi there,
I have a column of numbers separated by commas that is about 45,000 rows
long. I would like to sort the numbers in each individual row from small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.

 

[barnabel]

Here is a bit of code. modify it as you wish. It starts are row 1 and runs
until it finds 4 consective empty rows. It uses a very basic sort but if you
have less than 10 values on a row it shouldn't matter.

Peter Richardson

Option Explicit

Sub sortnum()
Dim currRow As Long
Dim blankCount As Integer
Dim acell() As String


currRow = 1
blankCount = 0
While blankCount < 5
If (IsEmpty(Cells(currRow, 1))) Then
blankCount = blankCount + 1
Else
blankCount = 0
acell = Split(Cells(currRow, 1), ",")
sortvals acell

Cells(currRow, 1) = Join(acell, ",")
End If
currRow = currRow + 1
Wend

End Sub

Sub sortvals(vals() As String)
Dim donesort As Boolean
Dim i As Integer
Dim holder As String

donesort = False
While Not donesort
donesort = True
For i = LBound(vals) To UBound(vals) - 1
If (CInt(vals(i)) > CInt(vals(i + 1))) Then
holder = vals(i)
vals(i) = vals(i + 1)
vals(i + 1) = holder
donesort = False
End If
Next
Wend

End Sub

 

[Peter Herman]

Thanks Peter,
Unfortunately, I am not that sophistocated. Is there not a function or
formula that I could use?

 

[JLatham]

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are overwritten) I
recommend that you create a copy of your workbook to use to verify that it
works properly. Actually, you can simply create a copy of the worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press [Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert |
Module and then copy the code into that module. Make any changes to column
identifier or starting row number (row with your 1st set of numbers to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run] button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on actual
number of entries and number of numbers in each entry, and physical setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") > 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min >= max, the list contains 0 or 1 items so it
' is sorted.
If min >= max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value >= med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

 

[Peter Herman]

sorry J,
this is kind of over my head. is there not a formula or function that would
do this?

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are overwritten) I
recommend that you create a copy of your workbook to use to verify that it
works properly. Actually, you can simply create a copy of the worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press [Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert |
Module and then copy the code into that module. Make any changes to column
identifier or starting row number (row with your 1st set of numbers to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run] button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on actual
number of entries and number of numbers in each entry, and physical setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") > 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min >= max, the list contains 0 or 1 items so it
' is sorted.
If min >= max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value >= med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

Hi there,
I have a column of numbers separated by commas that is about 45,000 rows
long. I would like to sort the numbers in each individual row from small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.

 

[Peo Sjoblom]

No, you would need code for this, if there would be a formula it would be a
very complicated
array formula and with 45000 rows the file would choke on itself. Array
formulas are notoriously slow.


You might want to try another method, first copy this particular column to a
new workbook so you don't do anything to the original, select the column and
do data>text to columns, select delimited click next and select comma as
delimiter and then click finish. Now all these values would be in different
columns, assume they start in A1, in other sheet put this in A1


=SMALL(Sheet1!1:1,COLUMNS($A$1:A1))


copy down 45000 rows and then copy across as many columns as needed


Finally put them together from a third sheet


=Sheet2!A1&", "&Sheet2!B1&", "&Sheet2!C1&", "&Sheet2!D1&", "&Sheet2!E1


then copy down 45000 rows and paste special as values


It might help it might not, it all depends on how many values there are in
your column,
if it's always 5 values with 5 commas the above should work


May I ask what would produce such a beast as your file and why you would
want to sort it?

 

[Peter Herman]

Thanks Peo,
I will try this and let you know. In answer to your question - I have 45,000
products on my website and this column of numbers represents various
categories on my website and many products have been slotted into various
categories. I am trying to edit some errors in the category data - so for
instance when ever an item is in category 17 I need to delete it from
category 1. I figured the best way to do this was to put the data in order
and then I could do a simple find and replace - when ever there is a data
value of 1,17 to just replace with 17. If there is a better way that would be
fine of course. Thanks for your interest. Zev

 

[JLatham]

Some super-guru of worksheet formulas might come up with something, but I'm
not The Guy. You've got a couple of things to deal with here: the entries in
each individual cell really aren't numbers, they are text representations of
numbers. In order to sort them properly they have to be broken out into
individual number groups and then sorted. If you leave them as text, then
they won't sort in the manner you expect. That's why I came up with a code
solution - and I think it's why Peo offered the code solution initially also.

Try his recommendation for trying to do the Text To Columns thing and see if
that doesn't help.

As a last resort? I've uploaded a workbook with the operational code that
you can simply copy your one worksheet into and run the code that way.
There's a single sheet in the workbook, it has a place to type in the column
letter where your data is and another cell to type in the number of the first
row on that sheet with data that needs sorting. Enter those values and click
the button and it will do its job and tell you when it's done.

Just click this link and choose to save it to your hard drive:
http://www.jlathamsite.com/uploads/for_PeterHerman.xls

Be sure you enable macros if asked to. If nothing happens when you click
the button, it probably means your Macro Security is set too high. To adjust
that,
choose Tools | Macro | Macro Security from the Excel menu bar. You may have
to pull down the list to see the Macro Security entry. Choose MEDIUM
setting. Close the window and close Excel. You have to close and reopen
Excel for the setting to become effective. Next time you open it, choose
[Enable] as the response to the question about allowing macros to run, and
then try again.

If you're using Excel 2007 - to get to the same place:
click the large Office button in the upper left of the Excel window.
click the [options] link at the lower right of the window that appears
click [Trust Center] in the left pane of the Excel Options window
then click the [Trust Center Settings] button
choose the 2nd option from the top: Disable all macros with notification
click [OK] and again close and reopen Excel
when the file opens, look near the top of the worksheet page and you should
see a notice that macros are disabled, but you'll have an option available to
enable them.

sorry J,
this is kind of over my head. is there not a formula or function that would
do this?

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are overwritten) I
recommend that you create a copy of your workbook to use to verify that it
works properly. Actually, you can simply create a copy of the worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press [Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert |
Module and then copy the code into that module. Make any changes to column
identifier or starting row number (row with your 1st set of numbers to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run] button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on actual
number of entries and number of numbers in each entry, and physical setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") > 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min >= max, the list contains 0 or 1 items so it
' is sorted.
If min >= max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value >= med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

Hi there,
I have a column of numbers separated by commas that is about 45,000 rows
long. I would like to sort the numbers in each individual row from small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.

 

[Peter Herman]

Hi J,
Thanks so much! you are a sweethart! your solution worked like a charm. If
you ever need any lighting for your home please do not hesitate to contact me
at
(e-mail address removed) for an equally sweet arrangement.
Peo - by the way your solution worked well on a test run but I was having
trouble running it on 45,000 records as the spreadsheet kept freezing.
Thanks,
Peter Zev

Some super-guru of worksheet formulas might come up with something, but I'm
not The Guy. You've got a couple of things to deal with here: the entries in
each individual cell really aren't numbers, they are text representations of
numbers. In order to sort them properly they have to be broken out into
individual number groups and then sorted. If you leave them as text, then
they won't sort in the manner you expect. That's why I came up with a code
solution - and I think it's why Peo offered the code solution initially also.

Try his recommendation for trying to do the Text To Columns thing and see if
that doesn't help.

As a last resort? I've uploaded a workbook with the operational code that
you can simply copy your one worksheet into and run the code that way.
There's a single sheet in the workbook, it has a place to type in the column
letter where your data is and another cell to type in the number of the first
row on that sheet with data that needs sorting. Enter those values and click
the button and it will do its job and tell you when it's done.

Just click this link and choose to save it to your hard drive:
http://www.jlathamsite.com/uploads/for_PeterHerman.xls

Be sure you enable macros if asked to. If nothing happens when you click
the button, it probably means your Macro Security is set too high. To adjust
that,
choose Tools | Macro | Macro Security from the Excel menu bar. You may have
to pull down the list to see the Macro Security entry. Choose MEDIUM
setting. Close the window and close Excel. You have to close and reopen
Excel for the setting to become effective. Next time you open it, choose
[Enable] as the response to the question about allowing macros to run, and
then try again.

If you're using Excel 2007 - to get to the same place:
click the large Office button in the upper left of the Excel window.
click the [options] link at the lower right of the window that appears
click [Trust Center] in the left pane of the Excel Options window
then click the [Trust Center Settings] button
choose the 2nd option from the top: Disable all macros with notification
click [OK] and again close and reopen Excel
when the file opens, look near the top of the worksheet page and you should
see a notice that macros are disabled, but you'll have an option available to
enable them.

sorry J,
this is kind of over my head. is there not a formula or function that would
do this?

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are overwritten) I
recommend that you create a copy of your workbook to use to verify that it
works properly. Actually, you can simply create a copy of the worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press [Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert |
Module and then copy the code into that module. Make any changes to column
identifier or starting row number (row with your 1st set of numbers to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run] button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on actual
number of entries and number of numbers in each entry, and physical setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") > 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min >= max, the list contains 0 or 1 items so it
' is sorted.
If min >= max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value >= med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

:

Hi there,
I have a column of numbers separated by commas that is about 45,000 rows
long. I would like to sort the numbers in each individual row from small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.

 

[Peo Sjoblom]

I can imagine that, then think of what would have happened if you would have
used a formula solution that was much slower than that. Glad Jerry's
solution worked for you

Peo

Hi J,
Thanks so much! you are a sweethart! your solution worked like a charm. If
you ever need any lighting for your home please do not hesitate to contact
me
at
(e-mail address removed) for an equally sweet arrangement.
Peo - by the way your solution worked well on a test run but I was having
trouble running it on 45,000 records as the spreadsheet kept freezing.
Thanks,
Peter Zev

Some super-guru of worksheet formulas might come up with something, but
I'm
not The Guy. You've got a couple of things to deal with here: the
entries in
each individual cell really aren't numbers, they are text representations
of
numbers. In order to sort them properly they have to be broken out into
individual number groups and then sorted. If you leave them as text,
then
they won't sort in the manner you expect. That's why I came up with a
code
solution - and I think it's why Peo offered the code solution initially
also.

Try his recommendation for trying to do the Text To Columns thing and see
if
that doesn't help.

As a last resort? I've uploaded a workbook with the operational code
that
you can simply copy your one worksheet into and run the code that way.
There's a single sheet in the workbook, it has a place to type in the
column
letter where your data is and another cell to type in the number of the
first
row on that sheet with data that needs sorting. Enter those values and
click
the button and it will do its job and tell you when it's done.

Just click this link and choose to save it to your hard drive:
http://www.jlathamsite.com/uploads/for_PeterHerman.xls

Be sure you enable macros if asked to. If nothing happens when you click
the button, it probably means your Macro Security is set too high. To
adjust
that,
choose Tools | Macro | Macro Security from the Excel menu bar. You may
have
to pull down the list to see the Macro Security entry. Choose MEDIUM
setting. Close the window and close Excel. You have to close and reopen
Excel for the setting to become effective. Next time you open it, choose
[Enable] as the response to the question about allowing macros to run,
and
then try again.

If you're using Excel 2007 - to get to the same place:
click the large Office button in the upper left of the Excel window.
click the [options] link at the lower right of the window that appears
click [Trust Center] in the left pane of the Excel Options window
then click the [Trust Center Settings] button
choose the 2nd option from the top: Disable all macros with notification
click [OK] and again close and reopen Excel
when the file opens, look near the top of the worksheet page and you
should
see a notice that macros are disabled, but you'll have an option
available to
enable them.

sorry J,
this is kind of over my head. is there not a formula or function that
would
do this?

:

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are
overwritten) I
recommend that you create a copy of your workbook to use to verify
that it
works properly. Actually, you can simply create a copy of the
worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press
[Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to
Insert |
Module and then copy the code into that module. Make any changes to
column
identifier or starting row number (row with your 1st set of numbers
to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu
toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run]
button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your
sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on
actual
number of entries and number of numbers in each entry, and physical
setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") > 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max
As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min >= max, the list contains 0 or 1 items so it
' is sorted.
If min >= max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value >= med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

:

Hi there,
I have a column of numbers separated by commas that is about 45,000
rows
long. I would like to sort the numbers in each individual row from
small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.

 

[JLatham]

Glad I could help. Looks like Peo's prediction of Excel choking on that size
of an array were spot-on.

Sometimes worksheet formulas are good, sometimes VBA - in this case VBA
turned out to be the Bigger (and better) Hammer. Not always the case.

Hi J,
Thanks so much! you are a sweethart! your solution worked like a charm. If
you ever need any lighting for your home please do not hesitate to contact me
at
(e-mail address removed) for an equally sweet arrangement.
Peo - by the way your solution worked well on a test run but I was having
trouble running it on 45,000 records as the spreadsheet kept freezing.
Thanks,
Peter Zev

Some super-guru of worksheet formulas might come up with something, but I'm
not The Guy. You've got a couple of things to deal with here: the entries in
each individual cell really aren't numbers, they are text representations of
numbers. In order to sort them properly they have to be broken out into
individual number groups and then sorted. If you leave them as text, then
they won't sort in the manner you expect. That's why I came up with a code
solution - and I think it's why Peo offered the code solution initially also.

Try his recommendation for trying to do the Text To Columns thing and see if
that doesn't help.

As a last resort? I've uploaded a workbook with the operational code that
you can simply copy your one worksheet into and run the code that way.
There's a single sheet in the workbook, it has a place to type in the column
letter where your data is and another cell to type in the number of the first
row on that sheet with data that needs sorting. Enter those values and click
the button and it will do its job and tell you when it's done.

Just click this link and choose to save it to your hard drive:
http://www.jlathamsite.com/uploads/for_PeterHerman.xls

Be sure you enable macros if asked to. If nothing happens when you click
the button, it probably means your Macro Security is set too high. To adjust
that,
choose Tools | Macro | Macro Security from the Excel menu bar. You may have
to pull down the list to see the Macro Security entry. Choose MEDIUM
setting. Close the window and close Excel. You have to close and reopen
Excel for the setting to become effective. Next time you open it, choose
[Enable] as the response to the question about allowing macros to run, and
then try again.

If you're using Excel 2007 - to get to the same place:
click the large Office button in the upper left of the Excel window.
click the [options] link at the lower right of the window that appears
click [Trust Center] in the left pane of the Excel Options window
then click the [Trust Center Settings] button
choose the 2nd option from the top: Disable all macros with notification
click [OK] and again close and reopen Excel
when the file opens, look near the top of the worksheet page and you should
see a notice that macros are disabled, but you'll have an option available to
enable them.

sorry J,
this is kind of over my head. is there not a formula or function that would
do this?

:

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are overwritten) I
recommend that you create a copy of your workbook to use to verify that it
works properly. Actually, you can simply create a copy of the worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press [Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert |
Module and then copy the code into that module. Make any changes to column
identifier or starting row number (row with your 1st set of numbers to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run] button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on actual
number of entries and number of numbers in each entry, and physical setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") > 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min >= max, the list contains 0 or 1 items so it
' is sorted.
If min >= max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) >= med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value >= med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo >= hi Then Exit Do
Loop
If lo >= hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

:

Hi there,
I have a column of numbers separated by commas that is about 45,000 rows
long. I would like to sort the numbers in each individual row from small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.