Hi All, Mar20
I'm building a utility function that sorts the rows of a 2 dimen
array on any up to all of its columns, (ascending or descending by wanted
column.) My 'first' version is working fine.

I'm enhancing the first version which handled only positive integer
and long data types, and string values to include + or - decimal numbers.
(e.g. for dates I use CStr and CDbl to create a string serial date number
40359.75123) The logic to align #'s with different quantities of decimals,
1.2
versus 1.12345 is coming along.

My problem is picking a 'best' way to still have numbers sort 'lower'
than alpha, while still being able to choose a binary or text comparison.

Background: the key is below, (spaces are for illustration here, (wish we
could post here in a non-proportional font) Let "s" = 1 space, other letters
are string values. Digits and the "." and "-" are themselves.
Below is a 3 column example of the key string for 4 array rows prior to
sorting the keys.

xywzsss xxxx 666 1 these keys have a Len of 15.
-123.00 DEfg 999 2 (rightmost 1-4 are the input 2 dim array row#'s
s000.55 aaaa 888 3 used to rewrite the input after the keys are
-123.00 defg 888 4 sorted.)

The key sequence after the ascending key sort should be row 4 first,
row 2 second, row 3 third, and row 1 last.

1. I need to get neg #'s to sort before positive. I used the ~ character,
(the sedilla ?) instead of the s in the .55 row 3 string, because a space
sorts before a - sign, BUT a vbTextCompare sort did NOT sort the ~ as
expected. A binary comparsison worked for that, but a binary compare does
not give desired results when sorting on alpha strings ("de" came after
"DE")

2. I think I have to enlarge the key when a column in the array has both
negative and positive numbers and string data. (I examine the sorting columns
in all rows of the input array to pick up their characteristics prior to
building
the keys.) lead character for string data = z negative # = N, positive #
= P

2a. For the leftmost data above, the 'new' key part would look like:
zxywzsss I think this gets me the sequence I want
N123.00s for either a text or binary comparison on
P000.55s the entire key.
N123.00s

Any suggestions would be most helpful. Thanks.

Neal

--
Neal Zimm

What you may want to do is to use two auxilary columns. Copy the alpha
data strings to one auxilary colunm and the numeric string to a 2nd
auxilary colun. Then by sort by rows using the two new columns instead
of the one column.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189217

http://www.thecodecage.com/forumz/chat.php