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
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