find next available number

M

mduntley

Board Regular
Joined
May 23, 2015
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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.
 
Sort by date Sort by votes
Based on your data try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG10Apr47
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] a [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
   Range("C2").Value = ""
    [COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
        [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]
            .Add Dn.Value
             [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
        .Sort: a = .toarray
          [COLOR="Navy"]For[/COLOR] n = 0 To UBound(a)
                [COLOR="Navy"]If[/COLOR] Not Temp = 0 [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] Not a(n) = 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
                Temp = a(n)
                Range("C2").Value = Temp + 1
           [COLOR="Navy"]Next[/COLOR] n
    
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
MickG said:
Based on your data try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG10Apr47
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] a [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
   Range("C2").Value = ""
    [COLOR=Navy]With[/COLOR] CreateObject("System.Collections.ArrayList")
        [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]
            .Add Dn.Value
             [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]Next[/COLOR]
        .Sort: a = .toarray
          [COLOR=Navy]For[/COLOR] n = 0 To UBound(a)
                [COLOR=Navy]If[/COLOR] Not Temp = 0 [COLOR=Navy]Then[/COLOR]
                    [COLOR=Navy]If[/COLOR] Not a(n) = 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
                Temp = a(n)
                Range("C2").Value = Temp + 1
           [COLOR=Navy]Next[/COLOR] n
    
    [COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...


This is great,

Would it be hard to copy it and generate the next 10 numbers, say from c2-c12?
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG10Apr50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 c = 1
   Range("C2:C12").Value = ""
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
        [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]
                Dic(Dn.Value) = Empty
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
     [COLOR="Navy"]If[/COLOR] Dic.Count > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] Application
                ReDim bool(.Min(Dic.keys) To .Max(Dic.keys) + 10) [COLOR="Navy"]As[/COLOR] Boolean
        [COLOR="Navy"]End[/COLOR] With
          
        [COLOR="Navy"]For[/COLOR] n = LBound(bool) To UBound(bool) + 10
            [COLOR="Navy"]If[/COLOR] Dic.exists(n) [COLOR="Navy"]Then[/COLOR]
              bool(n) = True
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
        
        [COLOR="Navy"]For[/COLOR] n = LBound(bool) To UBound(bool) + 10
            [COLOR="Navy"]If[/COLOR] bool(n) = False [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Cells(c, "c") = n
                [COLOR="Navy"]If[/COLOR] c = 11 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
     [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
mduntley said:
This is great,

Would it be hard to copy it and generate the next 10 numbers, say from c2-c12?
Click to expand...

Or you could put in C2
=INDEX(($B$2&"000"&TEXT(ROW($1:$999),"000"))*1,MATCH(AGGREGATE(14,6,1/(NOT(COUNTIF($A$1:$A$4000,($B$2&"000"&TEXT(ROW($1:$999),"000"))*1))*ROW($1:$999)),ROW(1:1)),1/(NOT(COUNTIF($A$1:$A$4000,($B$2&"000"&TEXT(ROW($1:$999),"000"))*1))*ROW($1:$999)),0))

confirm with Ctrl-Shift-Enter and drag down to C12
 
Upvote 0
Hi!

Try the Array Formulas below too.

Use Ctrl+Shift+Enter to enter the formulas

In C2 and copy down

=SMALL(IF(ISERROR(MATCH(ROW($1:$99)+$B$2*10^6,A$1:A$9,0)),ROW($1:$99)+$B$2*10^6),ROWS(C$2:C2))

In C14 and copy down

=SMALL(IF(ISERROR(MATCH(ROW($1:$99)+$B$14*10^6,A$13:A$22,0)),ROW($1:$99)+$B$14*10^6),ROWS(C$14:C14))


[TABLE="class: grid, width: 426"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]981000001[/TD]
[TD]INPUT[/TD]
[TD]NEXT AVAILABLE NUMBER NUMBER[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]981000002[/TD]
[TD="align: right"]981[/TD]
[TD="align: right"]981000005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]981000003[/TD]
[TD][/TD]
[TD="align: right"]981000011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]981000004[/TD]
[TD][/TD]
[TD="align: right"]981000012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]981000006[/TD]
[TD][/TD]
[TD="align: right"]981000013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]981000007[/TD]
[TD][/TD]
[TD="align: right"]981000014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]981000008[/TD]
[TD][/TD]
[TD="align: right"]981000015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]981000009[/TD]
[TD][/TD]
[TD="align: right"]981000016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]981000010[/TD]
[TD][/TD]
[TD="align: right"]981000017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]981000018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]981000019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]949000001[/TD]
[TD]INPUT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]949000002[/TD]
[TD="align: right"]961[/TD]
[TD="align: right"]961000002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]949000003[/TD]
[TD][/TD]
[TD="align: right"]961000003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]949000004[/TD]
[TD][/TD]
[TD="align: right"]961000004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]960000001[/TD]
[TD][/TD]
[TD="align: right"]961000005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]960000002[/TD]
[TD][/TD]
[TD="align: right"]961000006[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]960000003[/TD]
[TD][/TD]
[TD="align: right"]961000007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]960000004[/TD]
[TD][/TD]
[TD="align: right"]961000008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: right"]960000005[/TD]
[TD][/TD]
[TD="align: right"]961000009[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]961000001[/TD]
[TD][/TD]
[TD="align: right"]961000010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]961000011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********[/TD]
[TD]******[/TD]
[TD]****************************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Ps: I put the code for 1 to 99 - ROW($1:$99) - you can change it like you want.

Markmzz
 
Last edited:
Upvote 0
markmzz said:
Hi!

Try the Array Formulas below too.

Use Ctrl+Shift+Enter to enter the formulas

In C2 and copy down

=SMALL(IF(ISERROR(MATCH(ROW($1:$99)+$B$2*10^6,A$1:A$9,0)),ROW($1:$99)+$B$2*10^6),ROWS(C$2:C2))

In C14 and copy down

=SMALL(IF(ISERROR(MATCH(ROW($1:$99)+$B$14*10^6,A$13:A$22,0)),ROW($1:$99)+$B$14*10^6),ROWS(C$14:C14))


[TABLE="class: grid, width: 426"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD="align: right"]981000001
[/TD]
[TD]INPUT
[/TD]
[TD]NEXT AVAILABLE NUMBER NUMBER
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: right"]981000002
[/TD]
[TD="align: right"]981
[/TD]
[TD="align: right"]981000005
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: right"]981000003
[/TD]
[TD][/TD]
[TD="align: right"]981000011
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: right"]981000004
[/TD]
[TD][/TD]
[TD="align: right"]981000012
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: right"]981000006
[/TD]
[TD][/TD]
[TD="align: right"]981000013
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: right"]981000007
[/TD]
[TD][/TD]
[TD="align: right"]981000014
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: right"]981000008
[/TD]
[TD][/TD]
[TD="align: right"]981000015
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: right"]981000009
[/TD]
[TD][/TD]
[TD="align: right"]981000016
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: right"]981000010
[/TD]
[TD][/TD]
[TD="align: right"]981000017
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]981000018
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]981000019
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD="align: right"]949000001
[/TD]
[TD]INPUT
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD="align: right"]949000002
[/TD]
[TD="align: right"]961
[/TD]
[TD="align: right"]961000002
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD="align: right"]949000003
[/TD]
[TD][/TD]
[TD="align: right"]961000003
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD="align: right"]949000004
[/TD]
[TD][/TD]
[TD="align: right"]961000004
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD="align: right"]960000001
[/TD]
[TD][/TD]
[TD="align: right"]961000005
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD="align: right"]960000002
[/TD]
[TD][/TD]
[TD="align: right"]961000006
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD="align: right"]960000003
[/TD]
[TD][/TD]
[TD="align: right"]961000007
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD="align: right"]960000004
[/TD]
[TD][/TD]
[TD="align: right"]961000008
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD="align: right"]960000005
[/TD]
[TD][/TD]
[TD="align: right"]961000009
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD="align: right"]961000001
[/TD]
[TD][/TD]
[TD="align: right"]961000010
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]961000011
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***
[/TD]
[TD]*********
[/TD]
[TD]******
[/TD]
[TD]****************************
[/TD]
[TD]**
[/TD]
[/TR]
</tbody>[/TABLE]


Ps: I put the code for 1 to 99 - ROW($1:$99) - you can change it like you want.

Markmzz
Click to expand...

Nice - I thought there must be a simpler approach than the very long formula I concocted.
 
Upvote 0
Thank's Mick, can you help me on one part. I edit some information, i have the generate on a seperate page to look a bit cleaner, but i am having a problem. I am inserting my code. The Problem is that I want it to start populating in cell F8, but only 4 number appears. I want to see 10. Can you see what is going on?


Code: 
Sub MG10Apr50()Dim Rng As Range, Dn As Range, Temp As Long, c As Long, n As Long, Dic As Object
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 f = 7
   Range("f8:f18").Value = ""
    Set Rng = Range(Range("Data!A1"), Range("Data!A" & Rows.Count).End(xlUp))
    
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
        For Each Dn In Rng
            If Val(Left(Dn.Value, 3)) = Range("d8").Value Then
                Dic(Dn.Value) = Empty
            End If
        Next Dn
     If Dic.Count > 0 Then
        With Application
                ReDim bool(.Min(Dic.keys) To .Max(Dic.keys) + 10) As Boolean
        End With
          
        For n = LBound(bool) To UBound(bool) + 10
            If Dic.exists(n) Then
              bool(n) = True
            End If
        Next n
        
        For n = LBound(bool) To UBound(bool) + 10
            If bool(n) = False Then
                f = f + 1
                Cells(f, "f") = n
                If f = 11 Then Exit Sub
            End If
        Next n
     End If
End Sub


MickG said:
Try this:-
Regards Mick
Click to expand...


Markmzz, thank you for that formula, but i think VBA will work better because my data is huge (70,000 lines) and when i try it, it is taking a long time.
 
Upvote 0
Your very close, Replace 11 with 17 !!
Code: 
If f = [B][COLOR=#FF0000]17 [/COLOR][/B]Then Exit Sub
 
Upvote 0
mduntley said:
Markmzz, thank you for that formula, but i think VBA will work better because my data is huge (70,000 lines) and when i try it, it is taking a long time.
Click to expand...

Hi!

Yes, you have a big data.

So, use the new version below of the Array Formula to test the VBA code:

=SMALL(IF(ISERROR(MATCH(ROW($1:$7000)+$B$2*10^6,IF(--LEFT(A$1:A$70000,3)=B$2,A$1:A$70000),0)),
ROW($1:$7000)+$B$2*10^6),ROWS(C$2:C2))

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Tabstrip Usage
Replies
6
Views
200
Micron
M
K
  • Question Question
Stocktake of Barcoded Inventory in Excel using a Barcode Scanner
Replies
9
Views
359
Kobus Horn
K
L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
208
Alex Blakenburg
A
M
  • Question Question
find if the IBO number on Column "A"....
Replies
2
Views
111
midiyuga
M
A
  • Solved
query referring to an if / then, followed by subtraction.
Replies
6
Views
37
Fluff
Fluff

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top