How to find the next in a series of letters/numbers.

[Naranll]

Hi all, thanks in advance for any help you can provide. I'm hoping there is an elegant solution to my problem. Here it is.

I have a large list of numbers and letters that can extend from 1 to 99, then from AAA to ZZ9. My problem is that I need to find lowest open number in the range. I pull these suffixes from Extra into an array easily enough, and checking for gaps from 1 to 99 is not much of an issue, but I'm having some major difficulties trying to do the same if there are letters.

eg. I've pulled numbers from the file I'm working on, and all the numbers from 1-99 are taken as are the letters from AAA - NB2. Is there a way to elegantly determine the next number/letter?

 

[SkipVought]

Hi,

Are you really referring to NUMBERS, (stuff that you'll do arithmetic on) or are you referring to STRINGS, some of which contain numeric CHARACTERS?

If these are strings, which I believe they most likey are, then you have to be concerned with other issues.

Strings colate differetly than numbers. For instance, these string values are in ascending order...
[tt]
1
11
111
2
22
222
9
[/tt]
So your numeric characters from 1 to 99 is sorted (and I sorted this in a spreadsheet)
[tt]
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
28
29
3
30
31
32
33
34
35
36
37
38
39
4
40
41
42
43
44
45
46
47
48
49
5
50
51
52
53
54
55
56
57
58
59
6
60
61
62
63
64
65
66
67
68
69
7
70
71
72
73
74
75
76
77
78
79
8
80
81
82
83
84
85
86
87
88
89
9
90
91
92
93
94
95
96
97
98
99
[/tt]
So you're going to have to be much more specific about exactly what you need to do and WHY you are doing it.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naranll]

Sorry, for the incompleteness.

I need to compare them as a list of strings. They are record suffixes and right now to add a new record I have to manually inspect the entire list and find the first free suffix. I am aware of the problem with the numbers and have solved that at least to a workable level with formatting.

I am aware that this code could use some optomizing, and that I have probably done a dozen things horribly wrong, but it functions properly at the moment.

Also it is entirely possible that there is no easy or clear way to do what I want, in which case I'll just have to go back and do it the old fashioned way.

'***********************************************************
'*   Checks Suffix against legal suffixes to find a gap
'***********************************************************
  z = "00"                               'Sets initial number to check
For x = 1 to uBound(szIndex)             'Cycles through the list
szBit = Left(Right(szIndex(x), 3),2)     'Selects the Suffix
z = Format(z, "0#")                      'Formats Z so that it can be compared to szBit
  IF z = szBit Then                      'Will check every entry in the array against z,  
     iSuffix = TRUE                      'When found, goes on to the next
     z = z + 1                           
     IF z = "100" Then Exit Sub          'All numbers used, need to find 
     End IF                              'the next available letter
  Else 
     iSuffix = FALSE                     'If number is not found at all
  End IF                                 'set that number as the suffix
Next x
IF iSuffix = FALSE Then
   szSuffix = z
   msgbox "use this " + szSuffix         'Inform user of found Suffix.
End IF

 

[SkipVought]

So it appears that your suffix is 3 characters in length, since your ALPHA example is 3. YES?

Does that mean the your first series of sufixes are from 001 to 099?

I don't understand the sequence. The next logical value is 100. Why AAA?

You need to explain this very clearly, please.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naranll]

I understand the confusion, I've just been doing this so long I dont even really think it out any more. The order with which my procedures say I am to suffix a record is like so.

From 01 to 99... only two digits
From AA to Z9... only two digits
From AAA to ZZ9... only if all others are used.

 

[SkipVought]

So the first character from the right will always be numeric and the second & third characters from the right will always be alpha?

So these are suffixes that are directly in sequence...
[tt]
99 A1

A9 B1

Z9 AA1
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naranll]

That is correct.

 

[Naranll]

Sorry, not Correct

99 - A1 yes
A9 - B1 No A9 - BA
Z9 - AA1 No Z9 - AAA

 

[Naranll]

I apparently need to slow down, none of that was quite correct.

The correct order is
99 - AA
A9 - BA
Z9 - AAA
AA9 - ABA

 

[SkipVought]

Now I am REALLY confused!
[tt]
So 1 to 9 sorts BEFORE A
99 - AA

This indicates that A9 is grearer than AA????
A9 - BA
[/tt]
PLEASE get this straight!

I asked you...

So the first character from the right will always be numeric and the second & third characters from the right will always be alpha?

and you replied, "YES"!!!

Now the whole thing has changed SIGNIFICANTLY!!!

Do you REALLY know what you are doing?

I coded a solution based on your ORIGINAL requirements. Here it is. Modify to suite.

Function NextSuf(s As String) As String
    Dim b As String, i As Integer, inc As Integer
    
    inc = 0
    If IsNumeric(s) Then
        NextSuf = s * 1 + 1
        If NextSuf = "100" Then NextSuf = "A1"
    Else
        For i = Len(s) To 1 Step -1
            b = Mid(s, i, 1)
            Select Case b
                Case "9"
                    NextSuf = 1
                    inc = 1
                Case "1" To "8"
                    NextSuf = b * 1 + 1
                    inc = 0
                Case "Z"
                    If inc = 1 Then
                        NextSuf = "A" & NextSuf
                        inc = 1
                    Else
                        NextSuf = b & NextSuf
                    End If
                Case Else
                    NextSuf = Chr(Asc(b) + inc) & NextSuf
                    inc = 0
            End Select
        Next
        If inc = 1 Then
            NextSuf = "A" & NextSuf
        End If
    End If
End Function

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naranll]

Sorry about the trouble with my explanations. What you've provided is more or less exactly what I needed, Thank you.