find missing numbers in a sequence

[excelNewbie22]

hi!
how to found missing numbers in a sequence of numbers (row or column), with stating the range inside the formula, like 1-50 or 1-40

test
	A	B
1		formula for missing numbers?
2	3	1
3	5	2
4	7	4
5	9	6
6	11	8
7	12	10
8	15	13
9	22	14
10	27	16
11	33	17
12	34	18
13	35	19
14	40	20
15		21
16		23
17		24
18		25
19		26
20		28
21		29
22		30
23		31
24		32
25		36
26		37
27		38
28		39
test

 

[Fluff]

How about

Fluff.xlsm
	K	L
1		formula for missing numbers?
2	3	1
3	5	2
4	7	4
5	9	6
6	11	8
7	12	10
8	15	13
9	22	14
10	27	16
11	33	17
12	34	18
13	35	19
14	40	20
15		21
16		23
17		24
18		25
19		26
20		28
21		29
22		30
23		31
24		32
25		36
26		37
27		38
28		39
Data

Cell Formulas
Range	Formula
L2:L28	L2	=WORKDAY.INTL(0,SEQUENCE(MAX(K2:K20)-COUNT(K2:K20)),"0000000",K2:K20)
Dynamic array formulas.

 

[excelNewbie22]

it's good,
but it complete the missing numbers up to the last one entered (for the above example 40),
and i need it to completed to a fixed range, like always up to 50 regardless of the last value entered

 

[KOKOSEK]

Into B2:

=LET(a,SEQUENCE(MAX($A$2:$A$20),,1,1),b,IFERROR(MATCH(a,$A$2:$A$20,0),0),c,IF(b,0,a),FILTER(c,c<>0))

 

[hrayani]

I found this while searching the web - see if this helps

Book8
	A	B
1	3	1
2	5	2
3	7	4
4	9	6
5	11	8
6	12	10
7	15	13
8	22	14
9	27	16
10	33	17
11	34	18
12	35	19
13	40	20
14		21
15		23
16		24
17		25
18		26
19		28
20		29
21		30
22		31
23		32
24		36
25		37
26		38
27		39
Sheet1

Cell Formulas
Range	Formula
B1:B27	B1	=IFERROR(SMALL(IF(ISNA(MATCH(ROW(A$1:A$40),A$1:A$40,0)),ROW(A$1:A$40)),ROW(A1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

and i need it to completed to a fixed range, like always up to 50 regardless of the last value entered

Ok, how about

Fluff.xlsm
	J	K	L
1			formula for missing numbers?
2	1	3	1
3	50	5	2
4		7	4
5		9	6
6		11	8
7		12	10
8		15	13
9		22	14
10		27	16
11		33	17
12		34	18
13		35	19
14		40	20
15			21
16			23
17			24
18			25
19			26
20			28
21			29
22			30
23			31
24			32
25			36
26			37
27			38
28			39
29			41
30			42
31			43
32			44
33			45
34			46
35			47
36			48
37			49
38			50
39
Data

Cell Formulas
Range	Formula
L2:L38	L2	=WORKDAY.INTL(0,SEQUENCE(J3-J2+1-COUNT(K2:K20)),"0000000",K2:K20)
Dynamic array formulas.

 

[excelNewbie22]

thanks everybody!

fluff, you rock!

(only i used "50-1" instead of j3-j2)

 

[Fluff]

Glad we could help & thanks for the feedback.