Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
Thanks Peter,
Unfortunately, I am not that sophistocated. Is there not a function or formula that I could use? "barnabel" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
sorry J,
this is kind of over my head. is there not a formula or function that would do this? "JLatham" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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 datatext 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? -- Regards, Peo Sjoblom "Peter Herman" wrote in message ... Thanks Peter, Unfortunately, I am not that sophistocated. Is there not a function or formula that I could use? "barnabel" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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 "Peo Sjoblom" wrote: 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 datatext 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? -- Regards, Peo Sjoblom "Peter Herman" wrote in message ... Thanks Peter, Unfortunately, I am not that sophistocated. Is there not a function or formula that I could use? "barnabel" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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 he 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. "Peter Herman" wrote: sorry J, this is kind of over my head. is there not a formula or function that would do this? "JLatham" wrote: 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" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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 "Peter Herman" wrote in message ... 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 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 "JLatham" wrote: 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 he 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. "Peter Herman" wrote: sorry J, this is kind of over my head. is there not a formula or function that would do this? "JLatham" wrote: 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" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers in a row from small to large
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. "Peter Herman" wrote: 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 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 "JLatham" wrote: 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 he 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. "Peter Herman" wrote: sorry J, this is kind of over my head. is there not a formula or function that would do this? "JLatham" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
spreadsheet prints so small even with large font | Excel Discussion (Misc queries) | |||
Compound Lookup, Large/Small, Match... | Excel Worksheet Functions | |||
How do I show break in bar graph to show large and small numbers | Charts and Charting in Excel | |||
How do I show break in bar graph to show large and small numbers | Charts and Charting in Excel | |||
SMALL and LARGE | Excel Discussion (Misc queries) |