List Missing Numbers in a Sequence Using New Formulas?

[VBE313]

Is there a way to extract numbers in a sequence? For example, in the Sequence 1-10 in column A, 7 and 9 are missing. That is what my answer would be.

 

[StephenCrump]

Post #2 here may help?

Formula to find missing numbers.

 

[Dossfm0q]

Option

Missing numbers

Samples 29 32 9 5 25 12 13 24 16 14 0 22 3 7 12 19 5 Above sample data in Excel sheet 1 , column A:A 1) Need to find unique number in the samples and check missing number between 0 and 36

www.mrexcel.com

you can Expand Your Range ROW(INDIRECT("1:10")) 'ROW(INDIRECT("1:250"))

Book1
	A	B
1	Num	Mis
2	1	2
3	5	6
4	4	8
5	3	10
6	7
7
8
9	9
10
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:10")))/(COUNTIFS($A$2:$A$751,(ROW(INDIRECT("1:10"))))=0),ROWS($A$2:B2)),"")

 

[Peter_SSs]

Post #2 here may help?

Formula to find missing numbers.

Hi Stephen
One issue I notice with your suggestion in the other thread is that the spill range will always be N rows which could be a negative in some circumstances I would have thought.

Another option with minimal spill range here would be

21 02 10.xlsm
	A	B
1	1	7
2	2	9
3	3
4	4
5	5
6	6
7	8
8	10
9
10
Missing

Cell Formulas
Range	Formula
B1:B2	B1	=FILTER(SEQUENCE(10),ISNA(MATCH(SEQUENCE(10),A1:A10,0)),"")
Dynamic array formulas.