Dynamic arrays, circular arguments and all I want to due is increment a previous number

[vampsthevampyre]

Can anybody help, or is it just impossible what I'm asking excel to do.

What I'm looking to do, which the below sheet doesn't really so properly is as follows.

I have a table or dynamic array that has roulette numbers 0-36 and what I'm looking to do is have each number 0-36 in its own column that starts with number 36 and then increments by one on each number that is not the column header. If a number that matches the column header then the count resets to zero and then continues to increment as described above.

Every time I try to reference the previous numbers I'm getting a circular argument. Do I keep trying or is what I'm asking impossible to do.

Regards

Ian

roulette.xlsx
	B	C	D	E	F	G	H	I
3
4					0	1	2	3
5	Results		0		36	36	36	36
6	3		1		37	37	37	0
7	29		2		38	38	38	1
8	7		3		39	39	39	2
9	11		4		40	40	40	3
10	3		5		41	41	41	0
11	9		6		42	42	42	1
12	34		7		43	43	43	2
13	13		8		44	44	44	3
14	35		9		45	45	45	4
15	9		10		46	46	46	5
16	3		11		47	47	47	0
17	15		12		48	48	48	1
18	12		13		49	49	49	2
19	28		14		50	50	50	3
20	9		15		51	51	51	4
21	19		16		52	52	52	5
22	7		17		53	53	53	6
23	2		18		54	54	0	7
24	24		19		55	55	1	8
25	18		20		56	56	2	9
26	34		21		57	57	3	10
27	6		22		58	58	4	11
28	23		23		59	59	5	12
29	6		24		60	60	6	13
30	26		25		61	61	7	14
31	20		26		62	62	8	15
Sheet1

Cell Formulas
Range	Formula
D5:D31	D5	=SEQUENCE(COUNT(Table18[Results])+1,,0)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
3
4					0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36
5	Results		0		36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36
6	3		1		37	37	37	0	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37
7	29		2		38	38	38	1	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	0	38	38	38	38	38	38	38
8	7		3		39	39	39	2	39	39	39	0	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	1	39	39	39	39	39	39	39
9	11		4		40	40	40	3	40	40	40	1	40	40	40	0	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	2	40	40	40	40	40	40	40
10	3		5		41	41	41	0	41	41	41	2	41	41	41	1	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	3	41	41	41	41	41	41	41
11	9		6		42	42	42	1	42	42	42	3	42	0	42	2	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	4	42	42	42	42	42	42	42
12	34		7		43	43	43	2	43	43	43	4	43	1	43	3	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	5	43	43	43	43	0	43	43
13	13		8		44	44	44	3	44	44	44	5	44	2	44	4	44	0	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	6	44	44	44	44	1	44	44
14	35		9		45	45	45	4	45	45	45	6	45	3	45	5	45	1	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	7	45	45	45	45	2	0	45
15	9		10		46	46	46	5	46	46	46	7	46	0	46	6	46	2	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	8	46	46	46	46	3	1	46
16	3		11		47	47	47	0	47	47	47	8	47	1	47	7	47	3	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	9	47	47	47	47	4	2	47
17	15		12		48	48	48	1	48	48	48	9	48	2	48	8	48	4	48	0	48	48	48	48	48	48	48	48	48	48	48	48	48	10	48	48	48	48	5	3	48
18	12		13		49	49	49	2	49	49	49	10	49	3	49	9	0	5	49	1	49	49	49	49	49	49	49	49	49	49	49	49	49	11	49	49	49	49	6	4	49
19	28		14		50	50	50	3	50	50	50	11	50	4	50	10	1	6	50	2	50	50	50	50	50	50	50	50	50	50	50	50	0	12	50	50	50	50	7	5	50
20	9		15		51	51	51	4	51	51	51	12	51	0	51	11	2	7	51	3	51	51	51	51	51	51	51	51	51	51	51	51	1	13	51	51	51	51	8	6	51
21	19		16		52	52	52	5	52	52	52	13	52	1	52	12	3	8	52	4	52	52	52	0	52	52	52	52	52	52	52	52	2	14	52	52	52	52	9	7	52
22	7		17		53	53	53	6	53	53	53	0	53	2	53	13	4	9	53	5	53	53	53	1	53	53	53	53	53	53	53	53	3	15	53	53	53	53	10	8	53
23	2		18		54	54	0	7	54	54	54	1	54	3	54	14	5	10	54	6	54	54	54	2	54	54	54	54	54	54	54	54	4	16	54	54	54	54	11	9	54
24	24		19		55	55	1	8	55	55	55	2	55	4	55	15	6	11	55	7	55	55	55	3	55	55	55	55	0	55	55	55	5	17	55	55	55	55	12	10	55
25	18		20		56	56	2	9	56	56	56	3	56	5	56	16	7	12	56	8	56	56	0	4	56	56	56	56	1	56	56	56	6	18	56	56	56	56	13	11	56
26	34		21		57	57	3	10	57	57	57	4	57	6	57	17	8	13	57	9	57	57	1	5	57	57	57	57	2	57	57	57	7	19	57	57	57	57	0	12	57
27	6		22		58	58	4	11	58	58	0	5	58	7	58	18	9	14	58	10	58	58	2	6	58	58	58	58	3	58	58	58	8	20	58	58	58	58	1	13	58
28	23		23		59	59	5	12	59	59	1	6	59	8	59	19	10	15	59	11	59	59	3	7	59	59	59	0	4	59	59	59	9	21	59	59	59	59	2	14	59
29	6		24		60	60	6	13	60	60	0	7	60	9	60	20	11	16	60	12	60	60	4	8	60	60	60	1	5	60	60	60	10	22	60	60	60	60	3	15	60
30	26		25		61	61	7	14	61	61	1	8	61	10	61	21	12	17	61	13	61	61	5	9	61	61	61	2	6	61	0	61	11	23	61	61	61	61	4	16	61
31	20		26		62	62	8	15	62	62	2	9	62	11	62	22	13	18	62	14	62	62	6	10	0	62	62	3	7	62	1	62	12	24	62	62	62	62	5	17	62
Sheet5

Cell Formulas
Range	Formula
F4:AP31	F4	=LET(n,SEQUENCE(,37,0),VSTACK(n,SEQUENCE(,37,36,0),DROP(REDUCE("",n,LAMBDA(x,y,HSTACK(x,SCAN(36,Table18[Results],LAMBDA(a,b,IF(b=y,0,a+1)))))),,1)))
D5:D31	D5	=SEQUENCE(COUNT(Table18[Results])+1,,0)
Dynamic array formulas.

 

[Peter_SSs]

This is basically an extract of @Fluff's suggestion but simplifies the main formula a bit by removing the creation of the headings in F4, G4, .. (as it seemed to me that they might already be there anyway) & also removing the separate creation of the row of "36" values in row 5.
Anyway, I needed the practice at this sort of formula so it is presented as a possible option.
(If the headings in row 4 are already there but not produced by a spill formula as I have, then in the F5 formula, replace F4# with F4:AP4)

vampsthevampyre.xlsm
	B	C			F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
4					0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36
5	Results				36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36	36
6	3				37	37	37	0	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37	37
7	29				38	38	38	1	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	38	0	38	38	38	38	38	38	38
8	7				39	39	39	2	39	39	39	0	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	39	1	39	39	39	39	39	39	39
9	11				40	40	40	3	40	40	40	1	40	40	40	0	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	2	40	40	40	40	40	40	40
10	3				41	41	41	0	41	41	41	2	41	41	41	1	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	41	3	41	41	41	41	41	41	41
11	9				42	42	42	1	42	42	42	3	42	0	42	2	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	42	4	42	42	42	42	42	42	42
12	34				43	43	43	2	43	43	43	4	43	1	43	3	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	43	5	43	43	43	43	0	43	43
13	13				44	44	44	3	44	44	44	5	44	2	44	4	44	0	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	6	44	44	44	44	1	44	44
14	35				45	45	45	4	45	45	45	6	45	3	45	5	45	1	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	7	45	45	45	45	2	0	45
15	9				46	46	46	5	46	46	46	7	46	0	46	6	46	2	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	8	46	46	46	46	3	1	46
16	3				47	47	47	0	47	47	47	8	47	1	47	7	47	3	47	47	47	47	47	47	47	47	47	47	47	47	47	47	47	9	47	47	47	47	4	2	47
17	15				48	48	48	1	48	48	48	9	48	2	48	8	48	4	48	0	48	48	48	48	48	48	48	48	48	48	48	48	48	10	48	48	48	48	5	3	48
18	12				49	49	49	2	49	49	49	10	49	3	49	9	0	5	49	1	49	49	49	49	49	49	49	49	49	49	49	49	49	11	49	49	49	49	6	4	49
19	28				50	50	50	3	50	50	50	11	50	4	50	10	1	6	50	2	50	50	50	50	50	50	50	50	50	50	50	50	0	12	50	50	50	50	7	5	50
20	9				51	51	51	4	51	51	51	12	51	0	51	11	2	7	51	3	51	51	51	51	51	51	51	51	51	51	51	51	1	13	51	51	51	51	8	6	51
21	19				52	52	52	5	52	52	52	13	52	1	52	12	3	8	52	4	52	52	52	0	52	52	52	52	52	52	52	52	2	14	52	52	52	52	9	7	52
22	7				53	53	53	6	53	53	53	0	53	2	53	13	4	9	53	5	53	53	53	1	53	53	53	53	53	53	53	53	3	15	53	53	53	53	10	8	53
23	2				54	54	0	7	54	54	54	1	54	3	54	14	5	10	54	6	54	54	54	2	54	54	54	54	54	54	54	54	4	16	54	54	54	54	11	9	54
24	24				55	55	1	8	55	55	55	2	55	4	55	15	6	11	55	7	55	55	55	3	55	55	55	55	0	55	55	55	5	17	55	55	55	55	12	10	55
25	18				56	56	2	9	56	56	56	3	56	5	56	16	7	12	56	8	56	56	0	4	56	56	56	56	1	56	56	56	6	18	56	56	56	56	13	11	56
26	34				57	57	3	10	57	57	57	4	57	6	57	17	8	13	57	9	57	57	1	5	57	57	57	57	2	57	57	57	7	19	57	57	57	57	0	12	57
27	6				58	58	4	11	58	58	0	5	58	7	58	18	9	14	58	10	58	58	2	6	58	58	58	58	3	58	58	58	8	20	58	58	58	58	1	13	58
28	23				59	59	5	12	59	59	1	6	59	8	59	19	10	15	59	11	59	59	3	7	59	59	59	0	4	59	59	59	9	21	59	59	59	59	2	14	59
29	6				60	60	6	13	60	60	0	7	60	9	60	20	11	16	60	12	60	60	4	8	60	60	60	1	5	60	60	60	10	22	60	60	60	60	3	15	60
30	26				61	61	7	14	61	61	1	8	61	10	61	21	12	17	61	13	61	61	5	9	61	61	61	2	6	61	0	61	11	23	61	61	61	61	4	16	61
31	20				62	62	8	15	62	62	2	9	62	11	62	22	13	18	62	14	62	62	6	10	0	62	62	3	7	62	1	62	12	24	62	62	62	62	5	17	62
Sheet3

Cell Formulas
Range	Formula
F4:AP4	F4	=SEQUENCE(,37,0)
F5:AP31	F5	=DROP(REDUCE("",F4#,LAMBDA(x,y,HSTACK(x,SCAN(35,Table18[[#All],[Results]],LAMBDA(a,b,(a+1)*(b<>y)))))),,1)
Dynamic array formulas.

 

[djclements]

It's also possible to scan by column without REDUCE. Simply flip the array on its side with TRANSPOSE (because SCAN works in row major order), use an appropriate logical test to reset the scan at either the start or end of each row, then TRANSPOSE the results again to return the original orientation. For this particular scenario, the following should work:

=LET(
    n, SEQUENCE(,37,0),
    VSTACK(n,TRANSPOSE(SCAN(0,TRANSPOSE(VSTACK(n,Table18[Results]<>n)),LAMBDA(a,v,IF(ISNUMBER(v),36,(a+1)*v)))))
)