Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort by vba
Morning all.
A colleague and I are trying to make a macro that will sort a series of numbers in ascending order. However, we've found that what happens is that it does not take into account the number of characters are in each number. I.e., here is a sample 01234, 3245, v01456, 54678, 3765, 4568, 11234, 7534 what we've found is that the numbers are arranged as follows. 11234, 3234, 3765, 4568, 54678, 7534, 01234, v01456 What we'd like is: 3234, 3765, 4568, 7534, 01234, 11234, 54678, v01456 i.e., in an ascending, qty of digits, numbering. How would we accomplish doing this? If you have any questions to help better clarify, please ask. Our present code set is placing these numbers in the builtin document properties comment block. It's working fine except for this last part or ordering the numbers the way we need them ordered. Thank you in advance. Best. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort by vba
Add a column that contains the length of the cell text. Sort both columns by the text column (using xlTextAsNumbers). Sort both columns by the length column. Clear or delete the length column. -- Jim Cone Portland, Oregon USA (trial version of "Special Sort" add-in available upon request - 2 dozen ways to sort) "SteveDB1" wrote in message Morning all. A colleague and I are trying to make a macro that will sort a series of numbers in ascending order. However, we've found that what happens is that it does not take into account the number of characters are in each number. I.e., here is a sample 01234, 3245, v01456, 54678, 3765, 4568, 11234, 7534 what we've found is that the numbers are arranged as follows. 11234, 3234, 3765, 4568, 54678, 7534, 01234, v01456 What we'd like is: 3234, 3765, 4568, 7534, 01234, 11234, 54678, v01456 i.e., in an ascending, qty of digits, numbering. How would we accomplish doing this? If you have any questions to help better clarify, please ask. Our present code set is placing these numbers in the builtin document properties comment block. It's working fine except for this last part or ordering the numbers the way we need them ordered. Thank you in advance. Best. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort by vba
Hi Jim.
Thanks for the response. What we're doing is extracting the numeric strings from cells on a worksheet. We then want to order the numeric strings in an array, and that array then "sends" the strings to the built-in-document-properties comment box. You know, let me back up here a ways and start from the beginning. There are 3 macros that are linked together for the purpose of extracting the strings, storing them in an array, naming worksheets for each file, and then placing the numeric strings in the built-in-document-properties comments box. Everything works perfectly-- we've been working out the kinks for a while now-- and the last thing that's bothering us is the disordered numeric strings. Hency my post. If you want to see our code, let me know-- it's a long one, which is why I didn't post it. I figured it'd overwhelm anyone who looked at it. Thank you. "Jim Cone" wrote: Add a column that contains the length of the cell text. Sort both columns by the text column (using xlTextAsNumbers). Sort both columns by the length column. Clear or delete the length column. -- Jim Cone Portland, Oregon USA (trial version of "Special Sort" add-in available upon request - 2 dozen ways to sort) "SteveDB1" wrote in message Morning all. A colleague and I are trying to make a macro that will sort a series of numbers in ascending order. However, we've found that what happens is that it does not take into account the number of characters are in each number. I.e., here is a sample 01234, 3245, v01456, 54678, 3765, 4568, 11234, 7534 what we've found is that the numbers are arranged as follows. 11234, 3234, 3765, 4568, 54678, 7534, 01234, v01456 What we'd like is: 3234, 3765, 4568, 7534, 01234, 11234, 54678, v01456 i.e., in an ascending, qty of digits, numbering. How would we accomplish doing this? If you have any questions to help better clarify, please ask. Our present code set is placing these numbers in the builtin document properties comment block. It's working fine except for this last part or ordering the numbers the way we need them ordered. Thank you in advance. Best. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort by vba
You can do a search on array sorting and find quite a few solutions.
There some array sorting routines that are almost incomprehensible, blinding fast and very long. However, the Excel worksheet sort is as fast as most and is built-in to Excel. So I am sticking with my original post that could be implemented something like this... '-- Sub Sludge() 'Jim Cone - Portland, Oregon USA - Jan 2009 Dim rng As Range Dim arrTitles() As Variant ReDim arrTitles(1 To 50, 1 To 1) 'fill array - line below used for testing 'arrTitles() = Range("G1:G50").Value Set rng = Range(Cells(1, 2), Cells(UBound(arrTitles(), 1), 2)) rng.Value = arrTitles() With rng.Offset(0, 1) .Formula = "=Len(" & rng(1).Address(False, False) & ")" .Value = .Value End With 'now sort as per previous post rng.Resize(, 2).Sort key1:=rng, order1:=xlAscending, _ dataoption1:=xlSortTextAsNumbers rng.Resize(, 2).Sort key1:=rng.Offset(0, 1), order1:=xlAscending arrTitles() = rng.Value 'do something with sorted array End Sub '-- Note: If you have already named the worksheets and they are in the correct order, then you could just pick off the worksheet names and place them in the array. '-- Jim Cone Portland, Oregon USA (trial version of "Special Sort" Excel add-in available upon request - 2 dozen ways to sort) (remove xxx from email address) "SteveDB1" wrote in message Hi Jim. Thanks for the response. What we're doing is extracting the numeric strings from cells on a worksheet. We then want to order the numeric strings in an array, and that array then "sends" the strings to the built-in-document-properties comment box. You know, let me back up here a ways and start from the beginning. There are 3 macros that are linked together for the purpose of extracting the strings, storing them in an array, naming worksheets for each file, and then placing the numeric strings in the built-in-document-properties comments box. Everything works perfectly-- we've been working out the kinks for a while now-- and the last thing that's bothering us is the disordered numeric strings. Hency my post. If you want to see our code, let me know-- it's a long one, which is why I didn't post it. I figured it'd overwhelm anyone who looked at it. Thank you. "Jim Cone" wrote: Add a column that contains the length of the cell text. Sort both columns by the text column (using xlTextAsNumbers). Sort both columns by the length column. Clear or delete the length column. -- Jim Cone Portland, Oregon USA (trial version of "Special Sort" add-in available upon request - 2 dozen ways to sort) "SteveDB1" wrote in message Morning all. A colleague and I are trying to make a macro that will sort a series of numbers in ascending order. However, we've found that what happens is that it does not take into account the number of characters are in each number. I.e., here is a sample 01234, 3245, v01456, 54678, 3765, 4568, 11234, 7534 what we've found is that the numbers are arranged as follows. 11234, 3234, 3765, 4568, 54678, 7534, 01234, v01456 What we'd like is: 3234, 3765, 4568, 7534, 01234, 11234, 54678, v01456 i.e., in an ascending, qty of digits, numbering. How would we accomplish doing this? If you have any questions to help better clarify, please ask. Our present code set is placing these numbers in the builtin document properties comment block. It's working fine except for this last part or ordering the numbers the way we need them ordered. Thank you in advance. Best. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom sort by vba
Hi Jim.
Thank you. I'm going to read through, and get a better feel for your macro, and go from there for now. I'm also going to look up array sorting. I just tried on this newsgroup and came back nil. I did a quick search on google, and have yet to review the responses I got. I will definitely be back if I get stuck, or to let you know that I'm good. Thanks again for your time. Best. "Jim Cone" wrote: You can do a search on array sorting and find quite a few solutions. There some array sorting routines that are almost incomprehensible, blinding fast and very long. However, the Excel worksheet sort is as fast as most and is built-in to Excel. So I am sticking with my original post that could be implemented something like this... '-- Sub Sludge() 'Jim Cone - Portland, Oregon USA - Jan 2009 Dim rng As Range Dim arrTitles() As Variant ReDim arrTitles(1 To 50, 1 To 1) 'fill array - line below used for testing 'arrTitles() = Range("G1:G50").Value Set rng = Range(Cells(1, 2), Cells(UBound(arrTitles(), 1), 2)) rng.Value = arrTitles() With rng.Offset(0, 1) .Formula = "=Len(" & rng(1).Address(False, False) & ")" .Value = .Value End With 'now sort as per previous post rng.Resize(, 2).Sort key1:=rng, order1:=xlAscending, _ dataoption1:=xlSortTextAsNumbers rng.Resize(, 2).Sort key1:=rng.Offset(0, 1), order1:=xlAscending arrTitles() = rng.Value 'do something with sorted array End Sub '-- Note: If you have already named the worksheets and they are in the correct order, then you could just pick off the worksheet names and place them in the array. '-- Jim Cone Portland, Oregon USA (trial version of "Special Sort" Excel add-in available upon request - 2 dozen ways to sort) (remove xxx from email address) "SteveDB1" wrote in message Hi Jim. Thanks for the response. What we're doing is extracting the numeric strings from cells on a worksheet. We then want to order the numeric strings in an array, and that array then "sends" the strings to the built-in-document-properties comment box. You know, let me back up here a ways and start from the beginning. There are 3 macros that are linked together for the purpose of extracting the strings, storing them in an array, naming worksheets for each file, and then placing the numeric strings in the built-in-document-properties comments box. Everything works perfectly-- we've been working out the kinks for a while now-- and the last thing that's bothering us is the disordered numeric strings. Hency my post. If you want to see our code, let me know-- it's a long one, which is why I didn't post it. I figured it'd overwhelm anyone who looked at it. Thank you. "Jim Cone" wrote: Add a column that contains the length of the cell text. Sort both columns by the text column (using xlTextAsNumbers). Sort both columns by the length column. Clear or delete the length column. -- Jim Cone Portland, Oregon USA (trial version of "Special Sort" add-in available upon request - 2 dozen ways to sort) "SteveDB1" wrote in message Morning all. A colleague and I are trying to make a macro that will sort a series of numbers in ascending order. However, we've found that what happens is that it does not take into account the number of characters are in each number. I.e., here is a sample 01234, 3245, v01456, 54678, 3765, 4568, 11234, 7534 what we've found is that the numbers are arranged as follows. 11234, 3234, 3765, 4568, 54678, 7534, 01234, v01456 What we'd like is: 3234, 3765, 4568, 7534, 01234, 11234, 54678, v01456 i.e., in an ascending, qty of digits, numbering. How would we accomplish doing this? If you have any questions to help better clarify, please ask. Our present code set is placing these numbers in the builtin document properties comment block. It's working fine except for this last part or ordering the numbers the way we need them ordered. Thank you in advance. Best. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort with custom list | Excel Worksheet Functions | |||
Custom Sort | Excel Worksheet Functions | |||
Custom Sort | Excel Discussion (Misc queries) | |||
Custom Sort | Excel Programming | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) |