Sorting Question for Marshall Barton (Cont'd)

[Tom]

Marshall,

If I could ask for your assistance again....

I do have some very short strings I would like to accommodate, e.g.
1PG or 3PG or 10PG or 11PG, etc. These examples are sorting after
1??PG strings. The below code works very well except for part
numbers like 1PG or 11PG or 1-12345 or 11-12345. I have taken
your rules and attempted to modify them. Hopefully, they make sense for my
needs.

I deal with part numbers that begin all numeric, numeric with non-numeric
characters at the end, numeric that may have a non-numeric
character somewhere after the first numeric character. Then, I have
numbers that start with characters, continue with numeric and may end with
non-numeric characters. I have tried to modify your rules accordingly:

1) The initial text will be from 0 to 4 characters ending with a
non-numeric character(s).

To me, zero characters means the string begins numeric.

Numeric beginning strings may have an alpha or a hyphen or a DOT somewhere
after the first numeric digit, e.g. 1-12345 or 10-12345 or 100-12345 or
100.123456 or 45?12345 and needs to sort according to the number of digits
before the dash or DOT, e.g. 1-????? before 2-????? before 1?-????? before
1??-?????, etc. The 45?12345 example seems to sort just fine with the code
below.

Some numbers can have multiple hyphens and some numbers can have both
hyphens and a DOT.

2) The numeric part can be up to 12 digits and immediately follows the
initial text part. The numeric part may or may not be followed by a
trailing text part that starts with any non-numeric character.

3) The trailing text part is any remainder of the string starting with the
first non-numeric character after the numeric part.

If you could, please comment the code a little more.

If a string template would help, let me know!

Thanks so much!


Your previous reply:

Your new examples do not conform to those rules, so you must
come up with a different set of rules. My **guess** for the
rules is now:

1) The initial text will be from 0 to 3 characters ending
with a non-numeric character.

2) The numeric part can be up to 7 digits and immediately
follows the initial text part. The numeric part may or may
not be followed by a trailing text part that starts with any
non-numeric character.

3) The trailing text part is any remainder of the string
starting with the first non-numeric character after the
numeric part.

You must decide if my guesses at the rules is valid for all
of your various situations. Pay particular attention to the
3 that I used. If you might get very short strings (e.g. X3
or just 12) then you will need to modify rule 1) and the
code to accommodate the very short strings.

With all that said, here's my guess at a new procedure:

Public Function StandardizePartNum(PartNum)
Dim k As Integer, j As Integer

If IsNull(PartNum) Then
StandardizePartNum = Null
Exit Function
End If
'find last non-digit
For k = 3 To 1 Step -1
If Not Mid(PartNum, k, 1) Like "#" Then Exit For
Next k
'find numeric portion
For j = k + 1 To Len(PartNum)
If Not Mid(PartNum, j, 1) Like "#" Then Exit For
Next j
StandardizePartNum = _
Left(PartNum, k) & Space(4 - k) _
& Format(Val(Mid(PartNum, k + 1)), "0000000") _
& Mid(PartNum, j)
End Function

 

[Marshall Barton]

I think your first rule is inconsistent/ambiguous.

How can you determine the difference between the left,
middle and right portions of a part number like 1X2? It
seems to me that it could be either
L M R
1X 2
or
1 X2

You need to specify a set of rules that can be used to
separate the left, middle and right portions for every
possible part number. Without more definitive rules, I
don't see how we can write code to figure it out for you.

The fundamental concept that I was suggesting is to first
separate the portions (using code that implements your
rules) and then put them back together in a canonical form
that will sort the way you want.

 

[Tom]

Marsh,

I tried to do a better explanation based on your last message, but
apparently did not succeed. I probably just don't know how to put my ideas
into the correct programming terms or rules.

I am also confused on how to deal with and make one rule for strings that
begin with numbers and strings that begin with characters. I am thinking
we have to look at both major types of strings, but could be wrong.

I deal with two major types of part number strings:

One type is either all digits from 1 to 12 digits, or it is 1 to 12 digits
with non-numeric characters in the string and may have non-numeric
characters at the end.

So, in this case would you check to see if there is any non-numeric
characters. If not, pad to 8 digits and sort. This would take care of
pure numeric strings. If the string has non-numeric characters in the
string, go to the first non-numeric character, and pad the left to cover
say 5 digits? You could then allow for 8 digits in the middle after the
left non-numeric character and sort. The right would be any non-numeric
character after the numeric string in the middle.

The other string type begins with from one to four alpha characters, can
have as many as 8 digits following the characters, and may have some
non-numeric characters at the end.

So, in this case the left would be up to 4 characters, the middle would be
up to eight numeric digits, and the right would be any non-numeric
character to the right of the mid?

Again, my terminology probably leaves a lot to be desired.

Does this make any sense?

Thanks

Tom

 

[Marshall Barton]

What you are saying makes sense, but it is not detailed
enough. When I said that you need to come up with a set of
rules, I did not mean to imply that you must use a certain
number of rules. You can use as many rules as needed to
describe every possible part number regardless of its
length, leading or trailing digits, or nondigit characters.

To test your set of rules, take every different type of part
number and check how the rules determine the left, middle
and right portions. I think it would be beneficial if you
first made a list of all the different types of part numbers
and what their left, middle and right portions should be.
For example:
part num L M R
###### s ###### s
##aa s ## aa
##aa## ##aa ## s
######aa s ###### aa
##a######aa ##a ###### aa
. . .
where:
# is any digit
a is any nondigit
s is just spaces (i.e empty)

Then you/we can try to work out the rules to determine how
to deal with each type. After after we have an unambiguous
set of rules, we should try to see if some of the rules can
be combined to make a shorter list that still does the job.
Once you have what appears to be a minimal set of rules that
can successfully deal with every type of part number, then
we can try to translate the set of rules into code for a
function that will produce a sortable value.

Note that I am not certain that splitting the part numbers
into 3 portions will actually sort the way you want. It may
require 4 or maybe 5 portions depending on your exact
sorting requirements.

 

[Tom]

Marsh,

Your chart idea is something I was thinking about. I am more visual
sometimes, and it will help both of us. Thanks for the suggestion.

Pls give me few days as I have a business trip to Dallas on 9/7 for a week.
More shortly.

Thanks

Tom

 

[Tom]

Marsh,

Started a new thread below dated 9/15/2007.

Thx

Tom