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

[jbesclapez]

Hi MrExcel,

I have a table like this :

Group	Increment
1	1
1	1
1	1
1	1
2	1
2	2
2	1
2	2
2	1
2	2
3	1
3	2
3	3
4	1
4	2
4	3
4	4
4	1
4	2
4	3
4	4
5	1
5	2
5	3
5	4
5	5

<tbody>
</tbody>

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
2	1

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B2 =MOD( ROW() - MATCH(A2,$A$2:$A$27,0) - 1, A2 ) + 1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

	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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</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