Groups/Arrays/Patterns Help

[BlueNoteStan]

Strap in, this is gonna be long and possibly confusing.

I have to test and record the first and last data point in groups of 25. Each quarter, the iterations change, so in Q1, I'm testing 1, 25, 26, 50, 51, 75, 76, 100. . . In Q2, I'm testing 2, 24, 27, 49, 52, 74, 77, 99. . . and so on.

These pattern works great with the formula: =IF(MOD(ROW(),25)=1,"TEST",IF(MOD(ROW(),25)=0,"TEST","")) in the first column, and then =IF(MOD(ROW(),25)=2,"TEST",IF(MOD(ROW(),25)=24,"TEST","")) in the next and so on. This gives me a very pleasing pattern:

This would solve my problem, but in my groups of 25, some of them are not able to be tested. For example, in my first group of 25, only numbers 10, 11, 13, 15, 17, and 22 are able to be tested. I have a column (Column B on my actual sheet) dedicated to labeling which are able to be tested with strings "OCCUPIED" and "OPEN". I would like a formula that checks column B for the string "OPEN" in each group of 25 and capturing those rows in an array, then using this array to populate the string "TEST".

In this example, row 10 and 22 are the first and last rows in this array, they should have "TEST" in the column I copy the formula. In the next column, rows 11 and 17 are the next iteration of the pattern, and they should have the string "TEST" and so on.

I would like this pattern to be able to populate this string in up to 120 groups of 25 (basically, I'll need the formula to check every group of 25 for the range I copy it to).

I've been racking my brain for months trying to figure this out, but I can't seem to get the logic right and I keep getting a lot of errors. I would really appreciate any and all help with this formula!

 

[JamesCanale]

I'm not too sure about the whole output needed:

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Point	Test?	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10	OPEN	TEST			TEST			TEST			TEST			TEST
12	11	OPEN		TEST			TEST			TEST			TEST
13	12
14	13	OPEN			TEST			TEST			TEST			TEST
15	14
16	15	OPEN			TEST			TEST			TEST			TEST
17	16
18	17	OPEN		TEST			TEST			TEST			TEST
19	18
20	19
21	20
22	21
23	22	OPEN	TEST			TEST			TEST			TEST			TEST
24	23
25	24
26	25
Sheet19

Cell Formulas
Range	Formula
A2:A26	A2	=SEQUENCE(25)
C2:O26	C2	=LET(a,A2#,b,B2:B26,c,FILTER(a,b="OPEN"),dd,ROWS(c),d,INT(ROWS(c)/2+0.5),e,MMULT(c,SEQUENCE(1,13,1,0)),f,MOD(SEQUENCE(1,13)-1,d)+1,g,IF(ISEVEN(dd),(2*d+2)-f-1,(2*d+1)-f-1),h,INDEX(c,f),I,INDEX(c,g),j,IF((a=h)+(a=I),1,0),k,IF(j=1,"TEST",""),k)
Dynamic array formulas.

 

[BlueNoteStan]

I'm not too sure about the whole output needed:

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Point	Test?	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10	OPEN	TEST			TEST			TEST			TEST			TEST
12	11	OPEN		TEST			TEST			TEST			TEST
13	12
14	13	OPEN			TEST			TEST			TEST			TEST
15	14
16	15	OPEN			TEST			TEST			TEST			TEST
17	16
18	17	OPEN		TEST			TEST			TEST			TEST
19	18
20	19
21	20
22	21
23	22	OPEN	TEST			TEST			TEST			TEST			TEST
24	23
25	24
26	25
Sheet19

Cell Formulas
Range	Formula
A2:A26	A2	=SEQUENCE(25)
C2:O26	C2	=LET(a,A2#,b,B2:B26,c,FILTER(a,b="OPEN"),dd,ROWS(c),d,INT(ROWS(c)/2+0.5),e,MMULT(c,SEQUENCE(1,13,1,0)),f,MOD(SEQUENCE(1,13)-1,d)+1,g,IF(ISEVEN(dd),(2*d+2)-f-1,(2*d+1)-f-1),h,INDEX(c,f),I,INDEX(c,g),j,IF((a=h)+(a=I),1,0),k,IF(j=1,"TEST",""),k)
Dynamic array formulas.

This works great! Thank you so much! I never would've thought to make that formula. Is there a way to repeat this down all the way to row 3000 if necessary? The only way I can see to do it currently is to repeat the SEQUENCE formula in A26, and your LET formula in C26, and update the target cells for the new location. I would have to do this 172 times and that kinda sucks .

If that's possible, I'd appreciate the help!

 

[JamesCanale]

I'm not sure I understood your whole question - and going down to 3000 is part of it. You'd want it to go from 1 to 25 120 times? (172?) Where in column B you would have different sets of "OPEN" or not? for each group of 25? But each set of 25 is essentially it's own independent output?

However, if you just highlight A2:O26, and then drag that down to row 3000, it'll just take a minute, and you have 120 independent properly working groups of 25. Updating Column B would be tedious.

 

[BlueNoteStan]

I'm not sure I understood your whole question - and going down to 3000 is part of it. You'd want it to go from 1 to 25 120 times? (172?) Where in column B you would have different sets of "OPEN" or not? for each group of 25? But each set of 25 is essentially it's own independent output?

However, if you just highlight A2:O26, and then drag that down to row 3000, it'll just take a minute, and you have 120 independent properly working groups of 25. Updating Column B would be tedious.

I apologize for the lack of detail.

I would want the sequence to go from 1-3000, but each group of 25 to act like an independent working group. I've tried dragging down the A2:O26 range to 3000 and it functions just how I'd need it to, but the sequence in column A is just 1-25 repeating. If there's a way to increment the sequence so that the starting number is the previous starting number +1, that would be ideal.

And yes, updating column B with OPENs was tedious

If I haven't said it before, I appreciate you!

 

[JamesCanale]

This is easy enough to sort out - change A2 as below and the small change in C2, and then drag B2:O26 down to 3000.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Point	Test?	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10	OPEN	TEST			TEST			TEST			TEST			TEST
12	11	OPEN		TEST			TEST			TEST			TEST
13	12
14	13	OPEN			TEST			TEST			TEST			TEST
15	14
16	15	OPEN			TEST			TEST			TEST			TEST
17	16
18	17	OPEN		TEST			TEST			TEST			TEST
19	18
20	19
21	20
22	21
23	22	OPEN	TEST			TEST			TEST			TEST			TEST
24	23
25	24
26	25
27	26
28	27
29	28
30	29	Open	TEST				TEST				TEST				TEST
31	30
32	31
33	32	Open		TEST				TEST				TEST
34	33
35	34
36	35
37	36
38	37	Open			TEST				TEST				TEST
39	38	OPEN				TEST				TEST				TEST
40	39
41	40
42	41
43	42	OPEN				TEST				TEST				TEST
44	43
45	44	OPEN			TEST				TEST				TEST
46	45
47	46
48	47
49	48	OPEN		TEST				TEST				TEST
50	49
51	50	OPEN	TEST				TEST				TEST				TEST
Sheet19

Cell Formulas
Range	Formula
A2:A3001	A2	=SEQUENCE(3000)
C2:O51	C2	=LET(a,A2:A26,b,B2:B26,c,FILTER(a,b="OPEN"),dd,ROWS(c),d,INT(ROWS(c)/2+0.5),e,MMULT(c,SEQUENCE(1,13,1,0)),f,MOD(SEQUENCE(1,13)-1,d)+1,g,IF(ISEVEN(dd),(2*d+2)-f-1,(2*d+1)-f-1),h,INDEX(c,f),I,INDEX(c,g),j,IF((a=h)+(a=I),1,0),k,IF(j=1,"TEST",""),k)
Dynamic array formulas.