Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    Illinois, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel & different numbering systems (2003)

    I'm looking to be able to produce a spreadsheet that can calculate serial numbers in different numbering systems i.e. base33, base34, base36 etc.. These numbers would then be printed on rolls of labels. I would like to be able to enter the following data: 1. starting number (000001) 2. # of labels on a roll (10,000), 3. step # (increment by 1) 4. total number of labels to print (100,000). After entering these parameters, the output should look like this:

    ROLL# START# END #
    1 000001 009999
    2 100000 109999
    3 110000 119999 etc......for however many rolls the job entails (total qty 100,000 / labels on roll 10,000) = 10 rolls

    I've been to several websites and see UDF's to create serial numbers in alphanumeric sequences but I can't quite get the whole picuture to produce the output I'm looking for.....

    Any ideas will be much appreciated.

    Thanks,
    - BOB -

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel & different numbering systems (2003)

    Can you explain what the different number systems (base 33 etc.) have to do with the problem of finding the start and end numbers for the rolls?

    The attached workbook shows how you can use formulas to compute the start and end numbers.
    If you modify the values in the cells with a green background, the cells with a blue background will update themselves automatically.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Location
    Illinois, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & different numbering systems (2003)

    Hi Hans,
    Thanks for the reply. I was able to use the formula you created last year. I can get the first line to work but then when trying to calculate the second line, the formula bombs out because an entry like 0001F4 is not recognized as a number. I should have put in my example some numbers showing both numeric and alpha entries. Maybe I'm using the function you created correctly.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel & different numbering systems (2003)

    Er, "the formula you created last year" is a bit vague.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel & different numbering systems (2003)

    Perhaps he meant the UDF in <post:=623,687>post 623,687</post:>

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel & different numbering systems (2003)

    If you use the UDFs that hans created in <post#=623,687>post 623,687</post#>

    You could increment a value in A1by 1 using:

    <pre>=RIGHT("000000" & to33(from33(A1)+1),6)</pre>


    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel & different numbering systems (2003)

    Unfortunately I fail to see what that has to do with the question in this thread... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel & different numbering systems (2003)

    I couldn't completely either which is why I stuck with trying to directly answer the followup question with the UDF (I think) he alluded to... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Steve

  9. #9
    New Lounger
    Join Date
    Sep 2006
    Location
    Illinois, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & different numbering systems (2003)

    You're correct, that's the post I'm refering to. I can increment the value by changing the "+1+" to other another value. I'm trying to create a "schedule" of numbers for each roll of labels to be printed and only need to show a starting and ending number for each roll. So if the next row has a starting number that includes alpha characters (i.e. 00014F), the formula doesn't function any more because the 00014F is not recognized as a number.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel & different numbering systems (2003)

    Try the attached version.
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Sep 2006
    Location
    Illinois, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & different numbering systems (2003)

    Hi Hans,
    I tried this version and it works to calculate the starting and ending # of each roll just fine as long as the number is base 10. I didn't see it calculate anything when I tried a number like 1000F4 (base33). Thanks very much for the efforts but I'm just not skilled enough to figure out how to use your formulas or functions.

    - BOB -

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel & different numbering systems (2003)

    Use the From33 function. See attached version.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •