Removing consecutive number sets

[Ailana]

From a list of 6 digit number sets, is there a way to remove all sets that have a 4 or more digit consecutive sequence in it?

 

[JamesCanale]

Not sure what you are looking for exactly, but here is a 365 function that will find if a set has 4 or more consecutive digits in it:

MrExcelPlayground15.xlsx
	A	B
1	100000	TRUE
2	100002
3	100003
4	100005
5	100007
6	100009
7	100010
8	100011
9	100012
10	100016
11	100017
12	100021
13	100022
14	100023
15	100024
16	100025
17	100026
18	100030
Sheet22

Cell Formulas
Range	Formula
B1	B1	=LET(a,A1:A18,b,SEQUENCE(ROWS(a)-1),c,--(INDEX(a,b+1)-INDEX(a,b)=1),d,SEQUENCE(ROWS(c)-2),e,(INDEX(c,d)=1)*(INDEX(c,d+1)=1)*(INDEX(c,d+2)=1),SUM(e)>0)

 

[Ailana]

Not sure what you are looking for exactly, but here is a 365 function that will find if a set has 4 or more consecutive digits in it:

MrExcelPlayground15.xlsx
	A	B
1	100000	TRUE
2	100002
3	100003
4	100005
5	100007
6	100009
7	100010
8	100011
9	100012
10	100016
11	100017
12	100021
13	100022
14	100023
15	100024
16	100025
17	100026
18	100030
Sheet22

Cell Formulas
Range	Formula
B1	B1	=LET(a,A1:A18,b,SEQUENCE(ROWS(a)-1),c,--(INDEX(a,b+1)-INDEX(a,b)=1),d,SEQUENCE(ROWS(c)-2),e,(INDEX(c,d)=1)*(INDEX(c,d+1)=1)*(INDEX(c,d+2)=1),SUM(e)>0)

Thank you! But I’m not sure if that’s what I need. I might need to try to explain better…I have a list of numbers such as-
1 5 12 24 25 30
3 4 5 6 9 17
5 8 13 44 45 46
22 24 25 29 40 41

I want to find and remove all sets that have an unbroken sequence of 4 or more consecutive numbers like the second set of numbers listed above.
I hope that is clear

 

[JamesCanale]

Here is something with a helper column which I don't care for:

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H
25	1	5	12	24	25	30		TRUE
26	3	4	5	6	9	17		FALSE
27	5	8	13	44	45	46		TRUE
28	22	24	25	29	40	41		TRUE
29
30
31	1	5	12	24	25	30
32	5	8	13	44	45	46
33	22	24	25	29	40	41
Sheet22

Cell Formulas
Range	Formula
H25:H28	H25	=LET(a,A25:F25,b,SEQUENCE(COLUMNS(a)-1),c,--(INDEX(a,b+1)-INDEX(a,b)=1),d,SEQUENCE(ROWS(c)-2),e,(INDEX(c,d)=1)*(INDEX(c,d+1)=1)*(INDEX(c,d+2)=1),SUM(e)=0)
A31:F33	A31	=FILTER(A25:F28,H25:H28)
Dynamic array formulas.

 

[Ailana]

I’m not sure I care for this one either , lol. Each set of numbers is in its own cell on my sheet. So what I am trying to do is just get rid of any set with the 4+ digit criteria. I feel like it’s simpler to do than I think but I’m no guru lol.

 

[mikerickson]

If your six numbers are in A1:F1,
you are looking to find if D1-A1= 3 or E1-B1=3 or F1-B1 = 3

or if MIN(D1:F1 - A1:C1) = 3

 

[Ailana]

My six numbers sets are in cells columns (like A1:A50). How would I look in that case? Sorry but for some reason my brain is scrambling on this, could be because this problem infiltrated my dreams last night and messed up my sleep, lol. The example below is how the list is basically set up.

 

[JamesCanale]

Use the textsplit function to break your cells apart.

=LET(a,VALUE(TEXTSPLIT(H1," ")),b,SEQUENCE(COLUMNS(a)-1),c,--(INDEX(a,b+1)-INDEX(a,b)=1),d,SEQUENCE(ROWS(c)-2),e,(INDEX(c,d)=1)*(INDEX(c,d+1)=1)*(INDEX(c,d+2)=1),SUM(e)=0)

 

[Ailana]

Use the textsplit function to break your cells apart.

=LET(a,VALUE(TEXTSPLIT(H1," ")),b,SEQUENCE(COLUMNS(a)-1),c,--(INDEX(a,b+1)-INDEX(a,b)=1),d,SEQUENCE(ROWS(c)-2),e,(INDEX(c,d)=1)*(INDEX(c,d+1)=1)*(INDEX(c,d+2)=1),SUM(e)=0)

 

[Ailana]

I absolutely appreciate your assistance. I may just need to submit to the fact that I am not an excel expert even with your brilliant help. I can't seem to get it right but I thank you so much.