Increment a number untill N set in another cell and reset : Formula help plz.

[jbesclapez]

Hi MrExcel,

I have a table like this :
[TABLE="width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Increment[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Basically a group is in the first column.
Group one means there is only one data so I start at one and do not increment in the right column
Group 2 means there are 2 numbers so in the right column I start at one, then add one and reset... untill I reach a new group.
Group 3 means there are 3 number so in the right column I start at one, then 2 then 3 and I reset... untill the group is finished.

I tried without success this formula :=IF(MOD(ROW(), H3486) = 0, 1,MOD(ROW(), H3486)+1)

How would i do that?

 

[VBA Geek]

try this:

Book1
	B
2	1
Sheet4

Cell Formulas
Range	Formula
B2	=MOD(ROW() - MATCH(A2,$A$2:$A$27,0) - 1, A2 ) + 1

Book1
	A	B
1	Group
2	1	1
3	1	1
4	1	1
5	1	1
6	2	1
7	2	2
8	2	1
9	2	2
10	2	1
11	2	2
12	3	1
13	3	2
14	3	3
15	4	1
16	4	2
17	4	3
18	4	4
19	4	1
20	4	2
21	4	3
22	4	4
23	5	1
24	5	2
25	5	3
26	5	4
27	5	5
Sheet4

 

[jbesclapez]

try this:

	B

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MOD( ROW() - MATCH(A2,$A$2:$A$27,0) - 1, A2 ) + 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B
Group

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet4​

Thanks for your time

This formule uses a fix array from A2 to A27. I would like it to be "dynamic".

I changed your formula to that :
=MOD( ROW() - MATCH(H2,H:H,0) - 1, H2 ) + 1

but it is not working as expected on the group 2 it starts with a 2 instead of a 1

 

[jbesclapez]

OK, got it working with that :
=MOD( ROW() - MATCH(H2,H:H,0), H2 ) + 1

Thanks a lot VBA Geek