Creating a new filtered list from a dynamically created list based on previous numbers

[vampsthevampyre]

Morning All,

Back for help with a new question, I have a list of generated number sequences CR13 down to CR35, I'm then looking to filter this list based on if any numbers appear in the previous group of four numbers.

In the below example the first group of 4 numbers (0,1,2,3) is always accepted but the second set of 4 numbers (1,2,4,5) is rejected because numbers 1 and 2 appear in the previous accepted group of 4,
sequence 3 an 4 are accepted because none of the numbers appear in group 1,
group 5 is rejected because 17 and 20 appear in group 4 (third accepted group of numbers)
group 6 is rejected because 2,3 and 5 appear in sequence 1 and 3.

The final filtered list should look like the list in column DA13-DA18

I have highlighted the accepted numbers in yellow and rejected numbers in red.

Is there a formula that can perform this calculation

Any help would be appreciated

Regards

Ian

roulette.xlsx
	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA
12	ID	Sorted	Initial	Order	Actual	1st	2nd	3rd	4th		New List
13	1	0_1_2_3	TRUE	1	TRUE	0	1	2	3		0_1_2_3
14	2	1_2_4_5	FALSE		FALSE	1	2	4	5		4_5_7_8
15	3	4_5_7_8	FALSE	2	TRUE	4	5	7	8		16_17_19_20
16	4	16_17_19_20	FALSE	3	TRUE	16	17	19	20		29_30_32_33
17	5	17_18_20_21	FALSE		FALSE	17	18	20	21		11_12_14_15
18	6	2_3_5_6	FALSE		FALSE	2	3	5	6		22_23_25_26
19	7	5_6_8_9	FALSE		FALSE	5	6	8	9
20	8	8_9_11_12	FALSE		FALSE	8	9	11	12
21	9	13_14_16_17	FALSE		FALSE	13	14	16	17
22	10	29_30_32_33	FALSE	4	TRUE	29	30	32	33
23	11	7_8_10_11	FALSE		FALSE	7	8	10	11
24	12	11_12_14_15	FALSE	5	TRUE	11	12	14	15
25	13	14_15_17_18	FALSE		FALSE	14	15	17	18
26	14	19_20_22_23	FALSE		FALSE	19	20	22	23
27	15	20_21_23_24	FALSE		FALSE	20	21	23	24
28	16	26_27_29_30	FALSE		FALSE	26	27	29	30
29	17	28_29_31_32	FALSE		FALSE	28	29	31	32
30	18	32_33_35_36	FALSE		FALSE	32	33	35	36
31	19	10_11_13_14	FALSE		FALSE	10	11	13	14
32	20	31_32_34_35	FALSE		FALSE	31	32	34	35
33	21	22_23_25_26	FALSE	6	TRUE	22	23	25	26
34	22	23_24_26_27	FALSE		FALSE	23	24	26	27
35	23	25_26_28_29	FALSE		FALSE	25	26	28	29
Moving Corners

Cell Formulas
Range	Formula
CQ13:CQ35	CQ13	=SEQUENCE(COUNTA(CR13#))
CR13:CR35	CR13	=TRANSPOSE(SORTBY(Table3692654[[#Headers],[0_1_2_3]:[32_33_35_36]],OFFSET(AH12,MAX(Table3692654[Session])-1,,,23),1))
CS13:CS35	CS13	=IF(CQ13#=1,TRUE,FALSE)
CV13:CV35	CV13	=XLOOKUP(CR13#,Table54[Corner],Table54[1],"Missing",0)
CW13:CW35	CW13	=XLOOKUP(CR13#,Table54[Corner],Table54[2],"Missing",0)
CX13:CX35	CX13	=XLOOKUP(CR13#,Table54[Corner],Table54[3],"Missing",0)
CY13:CY35	CY13	=XLOOKUP(CR13#,Table54[Corner],Table54[4],"Missing",0)
Dynamic array formulas.

 

[DanteAmor]

I'm not sure if column A already has TRUE/FALSE values. If so, then it could be:

=FILTER(CR2:CR23,CU2:CU23="TRUE")

 

[vampsthevampyre]

I'm not sure if column A already has TRUE/FALSE values. If so, then it could be:

=FILTER(CR2:CR23,CU2:CU23="TRUE")

Sorry CU column was just for my reference,

 

[hagia_sofia]

Hello, two steps approach (i.e. with helper column) could be:

a) Insert into CS13 and drag down:

=LET(
a,--(TEXTSPLIT(CR13,,"_")),
b,--((TEXTSPLIT(TEXTJOIN("_",TRUE,CS$12:CS12),,"_")),
c,SUM(XLOOKUP(a,b,b,0)),
IF(c=0,CR13,""))

b) Use FILTER to obtain the new list.

But I guess you are looking for a single dynamic formula?

 

[DanteAmor]

Sorry CU column was just for my reference,

We can use that column.
Put the following formula in CU2 and copy down.

=LET(a,CV$1:CY1,IF((COUNTIF(a,CV2)+COUNTIF(a,CW2)+COUNTIF(a,CX2)+COUNTIF(a,CY2))=0,TRUE,FALSE))

Now in DA2:

=FILTER(CR2:CR23,CU2:CU23=TRUE)

Check: Rows 24 and 33 should be rejected:

 

[vampsthevampyre]

We can use that column.
Put the following formula in CU2 and copy down.

=LET(a,CV$1:CY1,IF((COUNTIF(a,CV2)+COUNTIF(a,CW2)+COUNTIF(a,CX2)+COUNTIF(a,CY2))=0,TRUE,FALSE))

Now in DA2:

=FILTER(CR2:CR23,CU2:CU23=TRUE)

Check: Rows 24 and 33 should be rejected:
View attachment 118256

Getting close, but it gives all false results, if you look at CV15 this line gives a FALSE result because of the 4 in CX14, I need some way of ignoring previous false results from the new countif line. and yes I have made a manual mistake in my example, well spotted

 

[DanteAmor]

Getting close, but it gives all false results

Sorry, I didn't realize, I had the data wrong on my sheet.

These are the correct formulas:
In CU13 and copy down:

=LET(a,CV$12:CY12,IF((COUNTIF(a,CV13)+COUNTIF(a,CW13)+COUNTIF(a,CX13)+COUNTIF(a,CY13))=0,TRUE,FALSE))

Now, jus in cell DA13:

=FILTER(CR13:CR35,CU13:CU35=TRUE)