FireCode math

[SLARRA]

Dear Excel Eggheads (and I say that with the most sincere admiration),

Here's a gnarly little problem set whose solutions elude me... We assign a unique string identifier, called a FireCode, to every federal wildland fire. The FireCode business and syntax rules are as follows:

1. Every FireCode is comprised of (only) 4 characters.
2. The eligible characters are the numeric digits 0 thru 9, and the letters (case insensitive) A thru Z, excluding I and O (to avoid confusion with the digits 1 and 0).
3. Every FireCode must include at least one letter character and one numeric digit. That is, no FireCode can be comprised entirely of letters or entirely of numbers.
4. FireCodes are issued in sequence. If you put all the eligible characters in a ordered list of issuance, the letter sequence (A thru Z, excluding I and O) comes first, followed by the number sequence (0 thru 9). Accordingly, the first FireCode ever issued was AAA0 (couldn't be AAAA because it has to have at least one digit), followed by AAA1. After AA99, the next FireCode issued was AAB0, and so on.

So... this basically boils down to a number system in Base 34 (24 letters + 10 digits). If it wasn't for those pesky business rules that render certain FireCode strings as ineligible (i.e. no 4-letter or 4-number sequences), I could handle the math. But, those rules really confound things!

There are 3 things that I'd like to do in Excel:
1. (most important): Calculate the number of FireCodes between two given FireCodes. Example: How many FireCodes were issued between EG1F and H54X?
2. Tell me what FireCode lies x positions from a given FireCode. Example: What will be the 20,000th FireCode issued after H54X?
3. (least important, surely involves code?): Generate the list, in proper sequence, of valid FireCodes that occur between two given FireCodes. Example: List all the FireCodes between EG1F and H54X.

This is not an urgent problem and I would not ask anyone to spend serious time on the solutions. But, if you have some ideas on how this could be approached, I'd love to hear them.

Thanks!

SDL

 

[shg]

Dooes AAB0 follow AAA9?

 

[shg]

       ---A---- ----------------B----------------
   1   EG1F     A1: Input                        
   2   H54X     A2: Input                        
   3    67,298  A3: =FireCodeDistance(A1, A2)    
   4    20,000  A4: Input                        
   5   J0DR     A5: =FireCodeOfst(A2, A4)        
   6                                             
   7   EG1F     A7: Input                        
   8   EG1G     A8 and down: =FireCodeOfst(A7, 1)
   9   EG1H                                      
  10   EG1J                                      
  11   EG1K                                      
  12   EG1L                                      
  13   EG1M                                      
  14   EG1N                                      
  15   EG1P                                      
  16   EG1Q                                      
  17   EG1R

 

[SLARRA]

Let's see... Yes, AAA9 is immediately followed by AAB0. Why? The right-hand-most digit (i.e. 34^0 position) advances one, but since 9 is the last in the sequence, this rolls forward to "A" and bumps the second-from-the-right digit (i.e. 34^1 position) to advance one, from "A" to "B". This results in AABA, but that's an illegal code because it does not contain a number. The next ones in line - AABB thru AABZ- are also illegal. But, you finally get to AAB0 (last character is a zero), which is the next valid FireCode.

 

[SLARRA]

Woot onya SHG! So, I'm looking at the output of your VBA code here, right? It looks like you may have solved the problem. How do I get to the code behind this output?

 

[shg]

I changed the name of the one function from FireCodeOfset to FireCodeAdd.

'======================= F I R E C O D E   M A T H =============================</SPAN>
' FireCode is a 4-character base 34 number, neither all alpha nor all numeric,</SPAN>
' using the symbols 0-9 and A-Z, excluding I and O</SPAN>
 
Function FireCodeAdd(sNum As String, ByVal iOfs As Long) As String</SPAN>
    ' shg 2012</SPAN>
   
    ' Returns the FireCode that is offset iOfs from sNum1</SPAN>
   
    Const sSym      As String = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ"</SPAN>
    Dim dNum        As Long</SPAN>
    Dim iSgn        As Double</SPAN>
 
    dNum = Base2Lng(sNum, sSym)</SPAN>
    iSgn = Sgn(iOfs)</SPAN>
 
    Do While iOfs</SPAN>
        dNum = dNum + iSgn</SPAN>
        FireCodeAdd = DblToBase(dNum, 34, 4, sSym)</SPAN>
        If IsFireCode(FireCodeAdd) Then iOfs = iOfs - iSgn</SPAN>
    Loop</SPAN>
End Function</SPAN>
 
Function FireCodeDistance(ByVal sNum1 As String, ByVal sNum2 As String) As Long</SPAN>
    ' shg 2012</SPAN>
   
    ' Returns the distance from sNum1 to sNum2</SPAN>
   
    Const sSym      As String = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ"</SPAN>
    Dim iSgn        As Long</SPAN>
    Dim iDif        As Long</SPAN>
 
    If sNum1 = sNum2 Then Exit Function</SPAN>
    If Not IsFireCode(sNum:=sNum1, bSure:=True) Then Exit Function</SPAN>
    If Not IsFireCode(sNum:=sNum2, bSure:=True) Then Exit Function</SPAN>
 
    iSgn = Sgn(Base2Lng(sNum2, sSym) - Base2Lng(sNum1, sSym))</SPAN>
 
    Do Until sNum1 = sNum2</SPAN>
        sNum1 = FireCodeAdd(sNum1, iSgn)</SPAN>
        iDif = iDif + iSgn</SPAN>
    Loop</SPAN>
   
    FireCodeDistance = iDif</SPAN>
End Function</SPAN>
 
Function IsFireCode(sNum As String, Optional bSure As Boolean = False) As Boolean</SPAN>
    Const sSym      As String = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ"</SPAN>
    Const sLikeA    As String = "[A-Z][A-Z][A-Z][A-Z]"</SPAN>
    Const sLike0    As String = "####"</SPAN>
    Dim i           As Long</SPAN>
   
    If bSure Then</SPAN>
        If Len(sNum) <> 4 Then Exit Function</SPAN>
        For i = 1 To 4</SPAN>
            If InStr(sSym, Mid(sSym, i, 1)) = 0 Then Exit Function</SPAN>
        Next i</SPAN>
    End If</SPAN>
   
    If sNum Like sLikeA Then Exit Function</SPAN>
    If sNum Like sLike0 Then Exit Function</SPAN>
 
    IsFireCode = True</SPAN>
End Function</SPAN>
 
Function Base2Lng(sNum As String, Optional ByVal sSym As String) As Long</SPAN>
    ' Converts sNum to a long</SPAN>
    Dim cBas        As Currency</SPAN>
    Dim cFac        As Currency</SPAN>
    Dim cNum        As Currency</SPAN>
    Dim i           As Long</SPAN>
 
    If Len(sSym) = 0 Then sSym = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"</SPAN>
    cBas = Len(sSym)</SPAN>
    cFac = 1</SPAN>
 
    For i = Len(sNum) To 1 Step -1</SPAN>
        cNum = cNum + (InStr(sSym, Mid(sNum, i, 1)) - 1) * cFac</SPAN>
        cFac = cFac * cBas</SPAN>
    Next i</SPAN>
   
    Base2Lng = cNum</SPAN>
End Function</SPAN>

 

[shg]

Missed a function:

Function DblToBase(ByVal d As Double, _</SPAN>
                   iBase As Long, _</SPAN>
                   Optional iLen As Long = 0, _</SPAN>
                   Optional ByVal sSym As String = "") As String</SPAN>
    ' shg 2007, 2012</SPAN>
    ' Returns the string conversion of d (0..1E15) to the specified iBase (2..36)</SPAN>
    Dim s0          As String</SPAN>
 
    If iBase < 2 Or iBase > 36 Then</SPAN>
        DblToBase = "Invalid base!"</SPAN>
 
    ElseIf d < 0 Or d > 1E+15 Then</SPAN>
        DblToBase = "Invalid number!"</SPAN>
 
    ElseIf Len(sSym) > 0 And Len(sSym) < iBase Then</SPAN>
        DblToBase = "Invalid symbol string!"</SPAN>
 
    Else</SPAN>
        If Len(sSym) = 0 Then sSym = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"</SPAN>
        s0 = Left(sSym, 1)</SPAN>
 
        Do</SPAN>
            DblToBase = Mid$(sSym, (d - Int(d / iBase) * iBase + 1), 1) & DblToBase</SPAN>
            d = Int(d / iBase)</SPAN>
        Loop While d</SPAN>
 
        If Len(DblToBase) < iLen Then</SPAN>
            DblToBase = String(iLen - Len(DblToBase), s0) & DblToBase</SPAN>
        End If</SPAN>
    End If</SPAN>
End Function</SPAN>

 

[SLARRA]

OK... Let me digest that a bit (I'm not a VBA guy).
And, I see why you asked about the sequence (AAB0 follow AAA9?) - because I made a mistake in my original post, where I said that AA99 is followed by AAB0. I think AA99 would actually be followed by ABA0. I'd like to confirm that with your VBA code, but it's blowing up with a "Compile Error: Sub or Function Not Defined" at this line:
FireCodeAdd = DblToBase(dNum, 34, 4, sSym)

As I noted, I'm not a VBA guy, so perhaps I'm doing something stupid...?

Excited - and thankful for you help so far!

SDL

 

[SLARRA]

OK.... Didn't see your updated post. VBA is working now, so now I'm evaluating....

Thanks!

SDL

 

[SLARRA]

Not quite sure we're there yet. I don't think the sequence is applied properly in your custom formulas.

In our FireCode system, the letters get assigned in series first, followed by the numbers (so A,B,C, ... ,X,Y,Z,0,1,3, ... ,7,8,9,A,B,C, etc). Using your custom formula - =FireCodeAdd(A4BZ,1) - the next FireCode after A4BZ is calculated (incorrectly?) to be A4C0, which in turn is followed (correctly) by A4C1.

Your formula's transition from A4BZ to A4C0 appears to be wrong. I think the next FireCodes in series after A4BZ should actually be A4B0, A4B1, ... , A4B9, A4CA, etc. I switched the order of the string series in your VBA code, recalculated the examples in my workbook, and that seemed to resolve the problem. But, since I don't know what I'm doing with VBA, would you mind re-posting the full set of corrected code that resolves this sequence problem?

Thanks!

SDL