find next available number

[mduntley]

I need to find a number that is not existing. My document is huge and I want to put in a number and it will give me a new code that isn't being used

Column A is the section that the existing code is on

B2 is where I want to put in a number.

c3 is where I want to see what is the next available number I can use.

 

[alansidman]

Show us an example of what you have. I am having a hard time understanding what you have. What would be the criteria for the next available number? Are the numbers you have sequential, random? Help us to help you by being more specific and provide examples. Your current request is to vague to understand.

 

[mduntley]

981000001	INPUT	NEXT AVAILABLE NUMBER NUMBER
981000002	981	981000005
981000003
981000004
981000006
981000007
981000008
981000009
981000010

<tbody>
</tbody>

I have made the table. Because there is no 981000005, c2 is showing that number. If there was a 981000005, then that field will be 981000011 because we don't have that number in that table.

 

[mduntley]

is this able to be done?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG09Apr18
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
    [COLOR="Navy"]If[/COLOR] Val(Left(Dn.Value, 3)) = Range("B2").Value [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not Dn.Value = Temp + 1 [COLOR="Navy"]Then[/COLOR]
                Range("C2").Value = Temp + 1
                [COLOR="Navy"]Exit[/COLOR] For
             [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Set[/COLOR] Temp = Dn
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mduntley]

Is there a way to do it as a formula or does it have a VBA?

 

[mduntley]

When i try this in my original value, it comes back with the previous number that i have. When I put in 960 it comes back with 949000005. Clarification, when i put in the 3 number, i have a formula to make it end with 000001, so if i put in 960, the result will be 960000001

Try this:-

[COLOR=Navy]Sub[/COLOR] MG09Apr18
[COLOR=Navy]Dim[/COLOR] rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, Temp [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] rng
    [COLOR=Navy]If[/COLOR] Val(Left(Dn.Value, 3)) = Range("B2").Value [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] Not Temp [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]If[/COLOR] Not Dn.Value = Temp + 1 [COLOR=Navy]Then[/COLOR]
                Range("C2").Value = Temp + 1
                [COLOR=Navy]Exit[/COLOR] For
             [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]End[/COLOR] If
  [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Set[/COLOR] Temp = Dn
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Can you show some Real data with some possible expected results, and clarify the purpose/formula for cell "B2".

 

[mduntley]

here is an example. I cant post all of the 3420 numbers.

949000001	INPUT
949000002	961	960000006
949000003
949000004
960000001
960000002
960000003
960000004
960000005
961000001

<tbody>
</tbody>

If I put in cell B2, i want the expected to be 961 I want 961000002, but it is giving me 960000006. If i put in 960 in b2, i want 960000006 but when i do it, i get 949000005

 

[mopp1]

Try
=INDEX((B2&"000"&TEXT(ROW($1:$999),"000"))*1,MATCH(0,COUNTIF($A$1:$A$4000,(B2&"000"&TEXT(ROW($1:$999),"000"))*1),0))

Enter with Ctrl-Shift-Enter (not just Enter)