last missing number

[excelNewbie22]

hi,
how can i tell the last one missing number from a fixed range, say 1 to 42, without knowing the range of rows? (only from where to check=starting point, like o2:t _)
see example below

test
	O	P	Q	R	S	T	U
1							last missing number
2	1	2	3	4	5	6
3	7	8	9	10	11	12
4	13	14	15	16	17	18
5	19	20	21	22	23	24
6	25	26	27	28	29	30
7	31	32	33	34	35	36
8	1	37	38	39	40	41	42
9	25	26	27	28	29	30
10
11
12	1	2	3	4	5	6
13	7	8	9	10	11	12
14	13	14	15	16	17	18
15	19	20	21	22	23	24
16	25	26	27	28	29	30
17	31	32	33	34	35	36
18	2	7	9	11	13	15
19	35	37	39	40	41	42	38
20	13	14	15	16	17	18
21
22
23	2	3	4	5	6	7
24	7	8	9	10	11	12
25	13	14	15	16	17	18
26	19	20	21	22	23	24
27	25	26	27	28	29	30
28	31	32	33	34	35	36
29	37	38	39	40	41	42
30	5	7	11	22	25	35
31	5	8	9	10	11	12
32	10	12	13	22	23	24	1
33	19	20	21	22	23	24
34	25	26	27	28	29	30
35	31	32	33	34	35	36
test

 

[Peter_SSs]

Like this?

23 07 24.xlsm
	O	P	Q	R	S	T	U
1							last missing number
2	1	2	3	4	5	6	42
3	7	8	9	10	11	12
4	13	14	15	16	17	18
5	19	20	21	22	23	24
6	25	26	27	28	29	30
7	31	32	33	34	35	36
8	1	37	38	39	40	41
9	25	26	27	28	29	30
10
11
12	1	2	3	4	5	6	38
13	7	8	9	10	11	12
14	13	14	15	16	17	18
15	19	20	21	22	23	24
16	25	26	27	28	29	30
17	31	32	33	34	35	36
18	2	7	9	11	13	15
19	35	37	39	40	41	42
20	13	14	15	16	17	18
21
22
23	2	3	4	5	6	7	1
24	7	8	9	10	11	12
25	13	14	15	16	17	18
26	19	20	21	22	23	24
27	25	26	27	28	29	30
28	31	32	33	34	35	36
29	37	38	39	40	41	42
30	5	7	11	22	25	35
31	5	8	9	10	11	12
32	10	12	13	22	23	24
33	19	20	21	22	23	24
34	25	26	27	28	29	30
35	31	32	33	34	35	36
Missing

Cell Formulas
Range	Formula
U2:U35	U2	=IF(AND(T1="",T2<>""),MATCH(0,COUNTIF(O2:INDEX(T:T,ROW()+MATCH(TRUE,T2:T$100="",0)-2),SEQUENCE(999)),0),"")

 

[excelNewbie22]

peter thanks, but is it based on the numbers to be in order? cause if is, my bad, i was trying to give an example which will make sense
sorry

here's another example, more accurate to my data

test
	O	P	Q	R	S	T	U
1							last missing number
2	3	8	11	15	28	38
3	4	12	14	16	20	39
4	1	5	22	26	27	34
5	2	6	24	31	36	37
6	10	13	21	23	30	41
7	7	17	18	25	29	35
8	9	19	32	33	40	42
9	2	3	10	15	20	42
10	1	4	8	14	21	34
11	5	17	29	35	40	41
12	9	11	13	25	32	33
13	7	12	23	26	28	30
14	16	19	24	27	36	39
15	6	18	22	31	37	38
16	1	9	12	15	32	36
17	4	13	18	19	33	38
18	2	7	10	20	26	34
19	5	11	21	27	30	42
20	14	22	25	28	39	41
21	3	6	8	17	24	37
22	16	23	29	31	35	40
23	5	14	18	20	22	31
24	6	10	16	24	25	30
25	4	7	11	19	28	33
26	1	9	26	27	36	41
27	3	13	17	21	37	38
28	15	23	35	39	40	42
29	2	8	12	29	32	34
30	1	6	8	19	23	41
31	10	25	26	28	32	38
32	4	16	24	31	34	40
33	9	12	15	17	29	37
34	2	5	13	22	30	35
35	7	11	14	20	36	42
36	3	18	21	27	33	39
37	8	14	15	25	27	33
38	1	3	18	28	36	37
39	7	13	19	32	39	42
40	2	4	11	26	29	31
41	6	12	20	24	30	40
42	9	16	17	21	35	41
43	5	10	22	23	34	38
test

 

[Anonymous1378]

Try this?

=XMATCH(TRUE,ISNA(XMATCH(SEQUENCE(42),TOCOL(O2:T9))),,-1)

 

[excelNewbie22]

Anonymous thanks, but it's aiming of an known range, like o2:t10, then will fill down, o2:t11 and etc and then it wouldn't allways return a the missing number since all 42 may includes inside this range

it's similar and better to what i was about to post, for trying to explain better what i'm after
notice the range in col h is different each time

test
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								last missing number
2	3	8	11	15	28	38		38
3	4	12	14	16	20	39		38
4	1	5	22	26	27	34		22	38
5	2	6	24	31	36	37		6	22	31	37	38
6	10	13	21	23	30	41		6	22	31	37	38
7	7	17	18	25	29	35		6	18	22	31	37	38
8	9	19	32	33	40	42		6	18	22	31	37	38
9	2	3	10	15	20	42		3
10	1	4	8	14	21	34		3	8
11	5	17	29	35	40	41
12	9	11	13	25	32	33
13	7	12	23	26	28	30
14	16	19	24	27	36	39
15	6	18	22	31	37	38
16	1	9	12	15	32	36
17	4	13	18	19	33	38
18	2	7	10	20	26	34
19	5	11	21	27	30	42
20	14	22	25	28	39	41
21	3	6	8	17	24	37
22	16	23	29	31	35	40
23	5	14	18	20	22	31
24	6	10	16	24	25	30
25	4	7	11	19	28	33
26	1	9	26	27	36	41
27	3	13	17	21	37	38
test

Cell Formulas
Range	Formula
G2	G2	=FILTER(SEQUENCE(42),ISNA(XMATCH(SEQUENCE(42),TOCOL(A2:F18))),"")
H2:H3,H9	H2	=TRANSPOSE(WORKDAY.INTL(0,SEQUENCE(42-1+1-COUNT(LET(t,VSTACK(A3:F13),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))))),"0000000",LET(t,VSTACK(A3:F13),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))))
H4:I4,H10:I10	H4	=TRANSPOSE(WORKDAY.INTL(0,SEQUENCE(42-1+1-COUNT(LET(t,VSTACK(A5:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))))),"0000000",LET(t,VSTACK(A5:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))))
H5:L5	H5	=TRANSPOSE(WORKDAY.INTL(0,SEQUENCE(42-1+1-COUNT(LET(t,VSTACK(A6:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))))),"0000000",LET(t,VSTACK(A6:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))))
H6:L6	H6	=TRANSPOSE(WORKDAY.INTL(0,SEQUENCE(42-1+1-COUNT(LET(t,VSTACK(A7:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))))),"0000000",LET(t,VSTACK(A7:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))))
H7:M7	H7	=TRANSPOSE(WORKDAY.INTL(0,SEQUENCE(42-1+1-COUNT(LET(t,VSTACK(A8:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))))),"0000000",LET(t,VSTACK(A8:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))))
H8:M8	H8	=TRANSPOSE(WORKDAY.INTL(0,SEQUENCE(42-1+1-COUNT(LET(t,VSTACK(A9:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))))),"0000000",LET(t,VSTACK(A9:F14),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))))
Dynamic array formulas.

 

[Anonymous1378]

So you don't actually just want the last missing number? Try the following in H2:

=FILTER(SEQUENCE(1,42),ISNA(XMATCH(SEQUENCE(1,42),TOCOL(A3:F13))),"")

 

[excelNewbie22]

So you don't actually just want the last missing number? Try the following in H2:

i'm, but sometimes the last row return several missing numbers

=FILTER(SEQUENCE(1,42),ISNA(XMATCH(SEQUENCE(1,42),TOCOL(A3:F13))),"")

won't work, again, when fill down, the range stayed fixed, a4:f14, a5:f15 and so on,
and i want the missing number from the row i'm at, say a50, to the closest row after, say a57 or a60... which will return all 41 numbers or close.... and therefor will give me the missing number/s

 

[Anonymous1378]

I can't fathom why you would want this, but try

=FILTER(SEQUENCE(1,42),ISNA(XMATCH(SEQUENCE(1,42),TOCOL(TAKE($A3:$F$27,MATCH(41,DROP(REDUCE("",SEQUENCE(ROWS($A3:$F$27)),LAMBDA(x,y,LET(z,ROWS(UNIQUE(TOCOL(TAKE($A3:$F$27,y)))),VSTACK(x,z)))),1),1))))),"")

 

[excelNewbie22]

Anonymous thanks again, when i change it to another range for checking another data, like that

=FILTER(SEQUENCE(1,37),ISNA(XMATCH(SEQUENCE(1,37),TOCOL(TAKE($C3:$H$100,MATCH(36,DROP(REDUCE("",SEQUENCE(ROWS($C3:$H$100)),LAMBDA(x,y,LET(z,ROWS(UNIQUE(TOCOL(TAKE($C3:$H$100,y)))),VSTACK(x,z)))),1),1))))),"")

it gives me only one result when i should have more
did i modify it wrong? the range of numbers are 1 to 37 instead 1 to 42
and the number of rows are about 1500 (only tried 100 for testing)

 

[Anonymous1378]

I don't see anything incorrect with the modified formula, you're going to have to show the data set you are testing it on.

And point out where you are getting the wrong result and what the expected result should be.