Sequential numbers

[KEITHC4217]

i am working on a form that is to generate a number based on two parts (ie 5600) where 56 is select from a list and then 00 is to be a sequential number starting at 00

when the 56 is selected and the second part is added i need the 00 the step up 1 if the number exists, this is so we dont get duplicates in the list and when more 56 numbers are created they step up 1 (5600, 5601, 5602, 5603 etc)

can anyone advise how to get the sequential number appended

thanks in advance

 

[danners430]

You could try something like this:

Function genNum(Selection As Integer) As Long


Static existingNumbers(0 To 9999) As Boolean
Dim i As Integer
Dim i2 As Long


genNum = -1             'Set default return value if no number is found


For i = 0 To 99
    
    i2 = Selection * 100
    i2 = i2 + i
    
    If existingNumbers(i2) = False Then
        genNum = i2
        existingNumbers(i2) = True
        Exit For
    End If


Next i


End Function

 

[KEITHC4217]

THANKS DANNERS

I have the code installed but am having difficulty getting it to perform
=VLOOKUP(I7,picklists!A:B,2,FALSE) is the formula I currently have that reports a number generated from a picklist to which i need to append the gennum to

the above gives 53 for example and need to append to it starting with 00
so the first 53 will be 5300 then 5301 and the first 51 will be 5100 then 5101

 

[Special-K99]

This all depends where you are storing the result so you need to specify clearly where your results will be on the spreadsheet (if you're outputting across columns this will require a different formula)

Try this

=VLOOKUP(I$7,picklists!A:B,2,FALSE)*100+ROWS($1:1)

This assumes each result will be placed UNDERNEATH the previous cell, e.g. A3 A4 A5 A6 etc
Note I7 is now I$7

UPDATE: I may have misinterpeted what you want. This simply generates a sequential number starting at 100 * the VLOOKUP.

 

[danners430]

Hi Keith,

I'm afraid you've completely stumped me now... what is it exactly you're trying to achieve?

From what I can gather, you're wanting to generate a number based on the first available two digit code for a given number. What is it the vlookup is searching for in column A, and what's stored in B that's being returned?

 

[KEITHC4217]

the vlookup allows the use to select the file type from column A ( mech or plumbing or elec)and assign a code accordingly column B ( 50, 60 or 70 series )

the appendment i am after is to then number the files in the different series so i get 5001 5002 and 6001 6002

the vlookup is working fine i cant get the sequential numbering for the different series to work

i hope that all make sense , i can email teh file if you wish

 

[danners430]

OK, I gotcha - column A is the "English" name for the document series, and column B is the actual code
What you could have, to prevent accidentally issuing a document number when you don't mean to do so, is to replace the vlookup with a simple dropdown box where the user selects the document type, and a button next to it that then generates a number dependant on the dropdown box. The code for the button would simply pull the number from the function above

Hope that makes sense! (I've just eaten lunch... it would surprise me!!)

 

[Special-K99]

the vlookup allows the use to select the file type from column A ( mech or plumbing or elec)and assign a code accordingly column B ( 50, 60 or 70 series )

the appendment i am after is to then number the files in the different series so i get 5001 5002 and 6001 6002

the vlookup is working fine i cant get the sequential numbering for the different series to work

i hope that all make sense , i can email teh file if you wish

which is what my (so far, ignored) formula does.
However it does not search for duplicates.

 

[KEITHC4217]

SpecialK99

i have not ignored your code honest i am trying it
thank youuuu

 

[danners430]

which is what my (so far, ignored) formula does.
However it does not search for duplicates.

I'm struggling to think of a way that the formula can search for duplicates... unless the documents are always listed chronologically, and old numbers are never deleted... But if those conditions were true it would be a much more efficient way of carrying out the function