find next available number

M

mduntley

Board Regular
Joined
May 23, 2015
Messages
134
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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))


ABCD
1981000001INPUTNEXT AVAILABLE NUMBER NUMBER
2981000002981981000005
3981000003981000011
4981000004981000012
5981000006981000013
6981000007981000014
7981000008981000015
8981000009981000016
9981000010981000017
10981000018
11981000019
12
13949000001INPUT
14949000002961961000002
15949000003961000003
16949000004961000004
17960000001961000005
18960000002961000006
19960000003961000007
20960000004961000008
21960000005961000009
22961000001961000010
23961000011
24
************************************************

<tbody>
</tbody>


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))


A
B
C
D
1
981000001
INPUT
NEXT AVAILABLE NUMBER NUMBER
2
981000002
981
981000005
3
981000003
981000011
4
981000004
981000012
5
981000006
981000013
6
981000007
981000014
7
981000008
981000015
8
981000009
981000016
9
981000010
981000017
10
981000018
11
981000019
12
13
949000001
INPUT
14
949000002
961
961000002
15
949000003
961000003
16
949000004
961000004
17
960000001
961000005
18
960000002
961000006
19
960000003
961000007
20
960000004
961000008
21
960000005
961000009
22
961000001
961000010
23
961000011
24
***
*********
******
****************************
**

<tbody>
</tbody>


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

T
  • Question
Problem with this code to put pictures into cells in Excel
Replies
1
Views
150
rlv01
R
B
  • Solved
Index match where duplicates exist
Replies
3
Views
180
Fluff
Fluff
A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
383
John_w
J
K
  • Question
VBA codes to replace all cells containing a certain value
Replies
2
Views
183
Snakehips
Snakehips
J
  • Question
Need a creative mind for help
Replies
4
Views
163
JRT2006
J

Forum statistics

Threads
1,216,799
Messages
6,132,770
Members
449,760
Latest member
letonuslepus

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