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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
194
Micron
M
L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
184
Alex Blakenburg
A
M
  • Question Question
find if the IBO number on Column "A"....
Replies
2
Views
105
midiyuga
M
M
  • Solved
Data Validation question
Replies
4
Views
255
Peter_SSs
Peter_SSs
C
  • Question Question
Macro run from Excel to transfer data in new Word doc won't output into Word
Replies
5
Views
374
cpu97
C

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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