custom sort by vba

[SteveDB1]

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.

 

[Jim Cone]

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"
<[email protected]>
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.

 

[SteveDB1]

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]

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"
<[email protected]>
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.

 

[SteveDB1]

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.