Need formula or VBA

[Russk68]

Hi
The result I need is in column LV and was manually entered. Column LZ are groups of numbers that always start from 1. The letter S also appears in this column and needs to be ignored.
Column LV starts at 1 and stops at 56. The next row starts at 1 and continues to 56 again.
I need a complete group of the same number to be within the same 56 rows. If it doesn't fit, the row number needs to restart at 1.
The white cell is the letter S just so it's not confused with the number 5.
I would prefer formulas but VB is ok!

I appreciate you!

2.14.23.1VBA.xlsm
	LV				LZ
4	1				1
5	2				1
6	3				1
7	4				1
8	5				1
9	6				1
10	7				1
11	8				1
12	9				1
13	10				1
14	11				1
15	12				1
16	13				S
17	14				2
18	15				2
19	16				2
20	17				2
21	18				2
22	19				2
23	20				2
24	21				2
25	22				2
26	23				2
27	24				S
28	25				3
29	26				3
30	27				3
31	28				3
32	29				3
33	30				3
34	31				3
35	32				3
36	33				3
37	34				3
38	35				S
39	36				4
40	37				4
41	38				4
42	39				4
43	40				4
44	41				4
45	42				4
46	43				4
47	44				4
48	45				4
49	46				4
50	47				4
51	48				4
52	49				4
53	50				S
54	1				5
55	2				5
56	3				5
57	4				5
58	5				5
59	6				5
60	7				5
61	8				5
62	9				5
Bundles

 

[StephenCrump]

The question is not clear, but I think this is what you want?

I've used a smaller number 12 to illustrate better. Just change the 12 to 56, and it will replicate the results you're looking for.

	LV				LZ
4	1				1
5	2				1
6	3				1
7	4				1
8	5				1
9	6				1
10	7				1
11	8				1
12	9				1
13	10				1
14	11				1
15	12				1
16	1				S
17	2				2
18	3				2
19	4				2
20	5				2
21	6				2
22	7				2
23	8				2
24	9				2
25	10				2
26	11				2
27	12				S
28	1				3
29	2				3
30	3				3
31	4				3
32	5				3
33	6				3
34	7				3
35	8				3
36	9				3
37	10				3
38	11				S
39	1				4
40	1				4
41	1				4
42	2				4
43	3				4
44	4				4
45	5				4
46	6				4
47	7				4
48	8				4
49	9				4
50	10				4
51	11				4
52	12				4
53	1				S
54	2				5
55	3				5
56	4				5
57	5				5
58	6				5
59	7				5
60	8				5
61	9				5
62	10				5
Sheet1

Cell Formulas
Range	Formula
LV5:LV62	LV5	=IF(LV4+MATCH(1,--(LZ5:LZ$63<>LZ5),)-1>12,1,LV4+1)

I also wanted to show what the formula does when there are too many values, i.e. it repeats "1" until there is room to finish the 1 to 12 sequence. Is that what you want to happen? Are or you perhaps confident there won't be more than 56 repeats of the same number?

11				S
1				4
1				4
1				4
2				4
3				4
4				4
5				4
6				4
7				4
8				4
9				4
10				4
11				4
12				4
1				S

 

[Russk68]

The question is not clear, but I think this is what you want?

I've used a smaller number 12 to illustrate better. Just change the 12 to 56, and it will replicate the results you're looking for.

	LV				LZ
4	1				1
5	2				1
6	3				1
7	4				1
8	5				1
9	6				1
10	7				1
11	8				1
12	9				1
13	10				1
14	11				1
15	12				1
16	1				S
17	2				2
18	3				2
19	4				2
20	5				2
21	6				2
22	7				2
23	8				2
24	9				2
25	10				2
26	11				2
27	12				S
28	1				3
29	2				3
30	3				3
31	4				3
32	5				3
33	6				3
34	7				3
35	8				3
36	9				3
37	10				3
38	11				S
39	1				4
40	1				4
41	1				4
42	2				4
43	3				4
44	4				4
45	5				4
46	6				4
47	7				4
48	8				4
49	9				4
50	10				4
51	11				4
52	12				4
53	1				S
54	2				5
55	3				5
56	4				5
57	5				5
58	6				5
59	7				5
60	8				5
61	9				5
62	10				5
Sheet1

Cell Formulas
Range	Formula
LV5:LV62	LV5	=IF(LV4+MATCH(1,--(LZ5:LZ$63<>LZ5),)-1>12,1,LV4+1)

I also wanted to show what the formula does when there are too many values, i.e. it repeats "1" until there is room to finish the 1 to 12 sequence. Is that what you want to happen? Are or you perhaps confident there won't be more than 56 repeats of the same number?

11				S
1				4
1				4
1				4
2				4
3				4
4				4
5				4
6				4
7				4
8				4
9				4
10				4
11				4
12				4
1				S

Hi Stephen
Not quite but maybe I can explain a little better.
LV and LZ are helper columns.
The purpose is to set page breaks without breaking matching sets of numbers between 2 pages.
Each page is 56 rows.
LZ is never the same size group of numbers.
LV needs to count down as close to 56 as possible without ending in the middle of a group of matching numbers.
The count in LV needs to always needs to end at a row with an S
The S represents a space between each group.
I need to get as many groups on 1 page as possible without breaking the groups.
I have code that will then reset the page breaks to a row that has an S.

 

[StephenCrump]

So perhaps something like this? (I've used 20, rather than 56, just to illustrate better)

	LV				LZ
4	1				1
5	2				1
6	3				1
7	4				1
8	5				1
9	6				1
10	7				1
11	8				1
12	9				1
13	10				1
14	11				1
15	12				1
16	13				S
17	1				2
18	2				2
19	3				2
20	4				2
21	5				2
22	6				2
23	7				2
24	8				2
25	9				2
26	10				2
27	11				S
28	1				3
29	2				3
30	3				3
31	4				3
32	5				3
33	6				3
34	7				3
35	8				3
36	9				3
37	10				3
38	11				S
39	1				4
40	2				4
41	3				4
42	4				4
43	5				4
44	6				4
45	7				4
46	8				4
47	9				4
48	10				4
49	11				4
50	12				4
51	13				4
52	14				4
53	15				S
54	1				5
55	2				5
56	3				5
57	4				5
58	5				5
59	6				5
60	7				5
61	8				5
62	9				5
Sheet1

Cell Formulas
Range	Formula
LV5:LV62	LV5	=IF(LV4+IFERROR(MATCH("s",LZ5:LZ$62,),ROWS(LZ5:LZ$62))>20,1,LV4+1)