Creating number tables skipping rows

[mackensteff]

It is possible to make a table like the following using a formula that I drag across and down:

The goal is to count sequentially across a row, then move to the next row return a zero, and the same with the third, and then pick up counting where it left off in the fourth row and so forth as the example table shows. I have tried MOD and INT functions but I can't seem to get it to work.

Thanks!

 

[Asbestos_Jen]

Something like this?

Book1
	B	C	D	E	F	G	H	I	J	K
2	7	8	9	10	11	12	13	14	15	16
3	0	0	0	0	0	0	0	0	0	0
4	0	0	0	0	0	0	0	0	0	0
5	17	18	19	20	21	22	23	24	25	26
6	0	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	0	0	0	0
8	27	28	29	30	31	32	33	34	35	36
Sheet1

Cell Formulas
Range	Formula
C2:K8	C2	=IF(B2>0,B2+1,0)
B3,B6	B3	=IF(B2>0,0,LARGE(B2:K2,1)+1)
B4,B7	B4	=IF(B3=0,0,LARGE($K$2:K3,1)+1)
B5,B8	B5	=IF(B4>0,0,LARGE($K$2:K4,1)+1)

 

[dreid1011]

Assuming your data starts in row A1 and you are only going across 10 columns, here is a single formula:

Book1
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	0	0
4	11	12	13	14	15	16	17	18	19	20
5	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0
7	21	22	23	24	25	26	27	28	29	30
8	0	0	0	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0	0	0	0
10	31	32	33	34	35	36	37	38	39	40
11	0	0	0	0	0	0	0	0	0	0
12	0	0	0	0	0	0	0	0	0	0
13	41	42	43	44	45	46	47	48	49	50
14	0	0	0	0	0	0	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0
16	51	52	53	54	55	56	57	58	59	60
17	0	0	0	0	0	0	0	0	0	0
18	0	0	0	0	0	0	0	0	0	0
19	61	62	63	64	65	66	67	68	69	70
20	0	0	0	0	0	0	0	0	0	0
21	0	0	0	0	0	0	0	0	0	0
22	71	72	73	74	75	76	77	78	79	80
Sheet1

Cell Formulas
Range	Formula
A1:J22	A1	=IF(MOD(ROW(),3)=1,COLUMNS($A:A)+FLOOR.MATH(INT(ROWS($1:1)*3.33),10),0)

If you need something more dynamic than a consistent 10 column range, then perhaps Jen's is the way to go or mine will have to be adjusted.

 

[dreid1011]

You can use mine for different column counts with the same row layout by changing the following:

=IF(MOD(ROW(),3)=1,COLUMNS($A:A)+FLOOR.MATH(INT(ROWS($1:1)*1.66),5),0)

Change the red number to your total columns divided by 3. Change the blue number to your total columns. Or you can put those calculations in cells and reference them so you don't have to keep changing the formula.

IE: 11 columns:

Book1
	L	M	N	O	P	Q	R	S	T	U	V
1	1	2	3	4	5	6	7	8	9	10	11
2	0	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	0	0	0
4	12	13	14	15	16	17	18	19	20	21	22
5	0	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0	0
7	23	24	25	26	27	28	29	30	31	32	33
8	0	0	0	0	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0	0	0	0	0
10	34	35	36	37	38	39	40	41	42	43	44
11	0	0	0	0	0	0	0	0	0	0	0
12	0	0	0	0	0	0	0	0	0	0	0
13	45	46	47	48	49	50	51	52	53	54	55
14	0	0	0	0	0	0	0	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0
16	56	57	58	59	60	61	62	63	64	65	66
17	0	0	0	0	0	0	0	0	0	0	0
18	0	0	0	0	0	0	0	0	0	0	0
19	67	68	69	70	71	72	73	74	75	76	77
20	0	0	0	0	0	0	0	0	0	0	0
21	0	0	0	0	0	0	0	0	0	0	0
22	78	79	80	81	82	83	84	85	86	87	88
Sheet1

Cell Formulas
Range	Formula
L1:V22	L1	=IF(MOD(ROW(),3)=1,COLUMNS($A:A)+FLOOR.MATH(INT(ROWS($1:1)*3.66),11),0)

IE: 5 columns:

Book1
	A	B	C	D	E
25	1	2	3	4	5
26	0	0	0	0	0
27	0	0	0	0	0
28	6	7	8	9	10
29	0	0	0	0	0
30	0	0	0	0	0
31	11	12	13	14	15
32	0	0	0	0	0
33	0	0	0	0	0
34	16	17	18	19	20
35	0	0	0	0	0
36	0	0	0	0	0
37	21	22	23	24	25
38	0	0	0	0	0
39	0	0	0	0	0
40	26	27	28	29	30
41	0	0	0	0	0
42	0	0	0	0	0
43	31	32	33	34	35
Sheet1

Cell Formulas
Range	Formula
A25:E43	A25	=IF(MOD(ROW(),3)=1,COLUMNS($A:A)+FLOOR.MATH(INT(ROWS($1:1)*1.66),5),0)

 

[Eric W]

You can do it with a single SPILL formula, so you don't need to drag anything:

Book1
	A	B	C	D	E	F	G	H	I	J
1	7	8	9	10	11	12	13	14	15	16
2	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	0	0
4	17	18	19	20	21	22	23	24	25	26
5	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0
7	27	28	29	30	31	32	33	34	35	36
8	0	0	0	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
A1:J9	A1	=LET(start,7,numrows,3,numcols,10,zerorows,2,zr,zerorows+1,s,SEQUENCE(numrows,numcols,start),allrows,numrows*zr,t,SEQUENCE(allrows,numcols,0),IF(MOD(t,zr*numcols)<numcols,INDEX(s,INT(t/zr/numcols)+1,SEQUENCE(,numcols)),0))
Dynamic array formulas.

If you look at the first 4 variables in the formula (start, numrows, numcols, zerorows), you can change those to whatever you want, or even set them to cells on the workbook if you need to change them dynamically.

 

[Peter_SSs]

Another single spill formula that you could test.
Starting number (strt), number of non-zero rows (non0rws), number of columns (cols) and number of zero rows between each "number" row (z0rws) can all be varied in the first part of the formula. I have provided two examples below.

24 02 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	7	8	9	10	11	12	13	14	15	16			1	2	3	4
2	0	0	0	0	0	0	0	0	0	0			0	0	0	0
3	0	0	0	0	0	0	0	0	0	0			0	0	0	0
4	17	18	19	20	21	22	23	24	25	26			0	0	0	0
5	0	0	0	0	0	0	0	0	0	0			5	6	7	8
6	0	0	0	0	0	0	0	0	0	0			0	0	0	0
7	27	28	29	30	31	32	33	34	35	36			0	0	0	0
8													0	0	0	0
9													9	10	11	12
10													0	0	0	0
11													0	0	0	0
12													0	0	0	0
13													13	14	15	16
14													0	0	0	0
15													0	0	0	0
16													0	0	0	0
17													17	18	19	20
18
Makearray

Cell Formulas
Range	Formula
A1:J7	A1	=LET(strt,7,non0rws,3,cols,10,z0rws,2,MAKEARRAY((z0rws+1)*(non0rws-1)+1,cols,LAMBDA(r,c,(cols*(r-1)/(z0rws+1)+strt+c-1)*(MOD(r,z0rws+1)=1))))
M1:P17	M1	=LET(strt,1,non0rws,5,cols,4,z0rws,3,MAKEARRAY((z0rws+1)*(non0rws-1)+1,cols,LAMBDA(r,c,(cols*(r-1)/(z0rws+1)+strt+c-1)*(MOD(r,z0rws+1)=1))))
Dynamic array formulas.

 

[Fluff]

Another two option (using the same variables as Peter) one with the zero rows at the bottom & one without.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	7	8	9	10	11	12	13	14	15	16
2	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	0	0
4	17	18	19	20	21	22	23	24	25	26
5	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0
7	27	28	29	30	31	32	33	34	35	36
8	0	0	0	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0	0	0	0
10
11	7	8	9	10	11	12	13	14	15	16
12	0	0	0	0	0	0	0	0	0	0
13	0	0	0	0	0	0	0	0	0	0
14	17	18	19	20	21	22	23	24	25	26
15	0	0	0	0	0	0	0	0	0	0
16	0	0	0	0	0	0	0	0	0	0
17	27	28	29	30	31	32	33	34	35	36
18
Data

Cell Formulas
Range	Formula
A1:J9	A1	=LET(strt,7,non0rws,3,cols,10,z0rws,2,WRAPROWS(TOCOL(EXPAND(SEQUENCE(non0rws,cols,strt),,(z0rws+1)*cols,0)),cols))
A11:J17	A11	=LET(strt,7,non0rws,3,cols,10,z0rws,2,DROP(WRAPROWS(TOCOL(EXPAND(SEQUENCE(non0rws,cols,strt),,(z0rws+1)*cols,0)),cols),-z0rws))
Dynamic array formulas.

 

[mackensteff]

These are all amazing and I can see benefits of each. However, Eric W, Peter_SSs, and Fluffs seem to invoke wizardy that I won't even pretend to understand.

Thanks everyone!

 

[Fluff]

Glad we could help & thanks for the feedback.