Find First Missing Number > 990000

[zero269]

Hello everyone,

I've been playing around with a few options that allow people to find missing numbers within a range of numbers.

One example is this one:
=SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))

The example above requires all values to be returned in a different column. I'm really just trying to get ONE number (the first/smallest missing number) … and only IF the number is greater than 990000.

I tried playing around with IF and MIN with the above formula, but no luck.

Brief details about my data:

I have two number ranges in my column I want to check against.

1. Testable books (#'s <990000)
2. Non-Testable Books (#'s >990000)

• The Testable Books have assigned Quiz numbers that are not changeable.
• The Non-Testable Books use my number range which starts at 990,000 as they do not conflict with the testable books range.

At times I find myself purging books from the master list… which consists of purging all data in the row to include the Quiz number in question.

If I purge a Non-Testable book, then I would like to reuse that number that's no longer in use. However, I do not always reuse it right away. Could be days or even weeks later.

Currently, I'm just using the MAX function +1 to show me what my Next Available Number is to use. However, if I can find the first (MIN) missing number greater than 990000, I think that would be optimal.

All the examples I've seen so far show a list of missing numbers. I'm hoping to get just that one number in a single cell if possible.

Any help or additional ideas would be greatly appreciated.

In the example below, I'm using a small sample of values for the number range I'm looking to find the first missing number in.

 

[Rick Rothstein]

Based on your picture, I guessed that your data starts in cell B4 and ends in cell B13. Note that in the formula, you must specify an end of range greater than than the last data cell's row so that you can find the blank after it in case there are no blank cells within the range... that is why I used B14 as the end cell (although any number larger than 14 would work), not B13.

=1+INDEX(B:B,MIN(IF(B4:B14="",ROW(B4:B14)))-1)

EDIT NOTE: Just realized that you could have numbers less than 990000 in the column, so here is a modification to the above formula that will ignore such values...

=LET(r,INDEX(A:A,IF(A4:A16="",ROW(A4:A16))-1),n,IFERROR(r,),1+MIN(IF(n>990000,n)))

 

[Peter_SSs]

and only IF the number is greater than 990000.

Based on your sample data, I'm not sure whether you are asking for the formula to actually check that?

23 01 26.xlsm
	B	C	D	E	F
1
2
3
4	990001	990001	990001	990001	800
5	990002	990002	990002	990002	801
6	990003			990003	802
7	990004	990004
8	990005	990005	990005	990005	990001
9	990006	990006	990006	990006	990002
10	990007	990007	990007	990007
11	990008	990008	990008	990008	990004
12	990009	990009	990009	990009	990005
13	990010	990010	990010	990010	990006
14
15
16
17	990011	990003	990003	990004	990003
Next Available

Cell Formulas
Range	Formula
B17:F17	B17	=LET(s,SEQUENCE(1000,,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,B4:B14,0)),1))

 

[Phuoc]

Or try

Book1
	A	B	C	D	E
1
2
3
4	990001	990001	990001	990001	800
5	990002	990002	990002	990002	801
6	990003			990003	802
7	990004	990004
8	990005	990005	990005	990005	990001
9	990006	990006	990006	990006	990002
10	990007	990007	990007	990007
11	990008	990008	990008	990008	990004
12	990009	990009	990009	990009	990005
13	990010	990010	990010	990010	990006
14
15
16
17	990011	990003	990003	990004	990003
Sheet1

Cell Formulas
Range	Formula
A17:E17	A17	=AGGREGATE(15,6,A4:A13/(A4:A13>990000)/(A5:A14=""),1)+1

 

[zero269]

=LET(r,INDEX(A:A,IF(A4:A16="",ROW(A4:A16))-1),n,IFERROR(r,),1+MIN(IF(n>990000,n)))

Rick,

Thanks for taking the time to look into this for me. I tested your code and adjusted the ranges for each of my four Test Columns.
Unfortunately, the results were showing numbers that were not missing. See my screenshot below where I compared three different proposals in this thread.

Your code for the first test column:

=LET(r,INDEX($C$5:$C$18,IF($C$5:$C$18="",ROW($C$5:$C$18))-1),n,IFERROR(r,),1+MIN(IF(n>990000,n)))

Based on your sample data, I'm not sure whether you are asking for the formula to actually check that?

Peter,

I tried your code too and tailored it to each of the four test columns.
You code returned the first missing number that was greater than 990000... which PERFECT.
That's awesome that this was achievable using code that only "looks" simple when compared to others I've played with today.
Absolutely amazing!

Your code for the first test column:

=LET(s,SEQUENCE(1000,,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,$C$5:$C$18,0)),1))

Thank you again for your time and expertise. I'm still working my way through the Learning VBA in Excel course on LinkedIn Learning and can't wait to get to the point where I can apply what I'm learning to my personal project.

Or try

Phuoc,
I tried your code too and adjusted the ranges for their respective columns. Unfortunately, it's producing #NUM errors for all four test columns.
Please see screenshot:

Your code for the first test column:

=AGGREGATE(15,6,$C$5:$C$18/($C$5:$C$18>990000)/($C$5:$C$18=""),1)+1

NOTE:
I added three numbers to each of the test columns that were below the range to validate it wouldn't return any numbers less than 990000.
Row 18 was used for first column only. The other three only went down to Row 17.

 

[Peter_SSs]

I tested your code and adjusted the ranges for each of my four Test Columns.

.. but not correctly. Same for Phuoc's formula

(Also note that based on your samples, none of the formula require $ signs for any of the ranges)

23 01 26.xlsm
	B	C	D	E	F
5		100	100	100	100
6		1000	1000	1000	1000
7		10000	10000	10000	10000
8		990001	990001	990001	990001
9		990002	990002	990002	990002
10		990003			990003
11		990004	990004
12		990005	990005	990005	990005
13		990006	990006	990006	990006
14		990007	990007	990007	990007
15		990008	990008	990008	990008
16		990009	990009	990009	990009
17		990010	990010	990010	990010
18
19
20
21	Rick	990011	990003	990003	990004
22	Peter	990011	990003	990003	990004
23	Phuoc	990011	990003	990003	990004
Next Available (2)

Cell Formulas
Range	Formula
C21:F21	C21	=LET(r,INDEX(C:C,IF(C5:C18="",ROW(C5:C18))-1),n,IFERROR(r,),1+MIN(IF(n>990000,n)))
C22:F22	C22	=LET(s,SEQUENCE(1000,,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,C5:C19,0)),1))
C23:F23	C23	=AGGREGATE(15,6,C5:C17/(C5:C17>990000)/(C6:C18=""),1)+1

 

[Phuoc]

Phuoc,
Your code for the first test column:

=AGGREGATE(15,6,$C$5:$C$18/($C$5:$C$18>990000)/($C$5:$C$18=""),1)+1

No, it's not . Should be:


=AGGREGATE(15,6,$C$5:$C$18/($C$5:$C$18>990000)/($C$6:$C$19=""),1)+1

 

[zero269]

Hi Peter,

.. but not correctly. Same for Phuoc's formula

Good catch!
I didn't realize that some started at Row 4 and 5 at the top... I made corrections and all but one is working now... but that's only due to a conflict with values above and below the Table in my original workbook.

(Also note that based on your samples, none of the formula require $ signs for any of the ranges)

Out of habit I locked the ranges... I didn't foresee any potential conflicts.

I can't use the one that results in 1 above because it references the entire column and I have values below the Table in my original workbook.

 

[zero269]

No, it's not . Should be:


=AGGREGATE(15,6,$C$5:$C$18/($C$5:$C$18>990000)/($C$6:$C$19=""),1)+1

Sorry about that. Peter noticed that too and I was able to correct my testing...

 

[Peter_SSs]

I can't use the one that results in 1 above because it references the entire column and I have values below the Table in my original workbook.

How is that a problem?

23 01 26.xlsm
	B	C
1
2
3
4
5		100
6		1000
7		10000
8		990001
9		990002
10		990003
11		990004
12		990005
13		990006
14		990007
15		990008
16		990009
17		990010
18
19
20
21	Rick	990011
22	Peter	990011
23	Phuoc	990011
24
25		Data
26		5000000
27		56
28		990011
29		990012
Next Available (2)

Cell Formulas
Range	Formula
C21	C21	=LET(r,INDEX(C:C,IF(C5:C18="",ROW(C5:C18))-1),n,IFERROR(r,),1+MIN(IF(n>990000,n)))
C22	C22	=LET(s,SEQUENCE(1000,,990001),AGGREGATE(15,6,s/ISNA(MATCH(s,C5:C19,0)),1))
C23	C23	=AGGREGATE(15,6,C5:C17/(C5:C17>990000)/(C6:C18=""),1)+1