Create numbering sequence within groups

[FryGirl]

I'm currently using =IF(B1=B2,C1,MAX($C$1:C1)+1) in column C and it works fine to establish a sequential numbering, but not sure how to adjust for a new year.

Data Range

	A​	B​	C​
1​	Year​	Team​	Series​
2​	2018​	Team1​	1​
3​	2018​	Team1​	1​
4​	2018​	Team1​	1​
5​	2018​	Team2​	2​
6​	2018​	Team2​	2​
7​	2018​	Team2​	2​
8​	2018​	Team2​	2​
9​	2018​	Team3​	3​
10​	2018​	Team3​	3​
11​	2018​	Team3​	3​
12​	2018​	Team3​	3​
13​	2019​	Team1​	4​
14​	2019​	Team1​	4​
15​	2019​	Team2​	5​
16​	2019​	Team2​	5​
17​	2019​	Team2​	5​
18​	2019​	Team2​	5​
19​	2019​	Team2​	5​
20​	2019​	Team3​	6​
21​	2019​	Team3​	6​
22​	2019​	Team3​	6​
23​	2019​	Team3​	6​

<tbody>
</tbody>

Would like
Data Range

	A​	B​	C​
1​	Year​	Team​	Series​
2​	2018​	Team1​	1​
3​	2018​	Team1​	1​
4​	2018​	Team1​	1​
5​	2018​	Team2​	2​
6​	2018​	Team2​	2​
7​	2018​	Team2​	2​
8​	2018​	Team2​	2​
9​	2018​	Team3​	3​
10​	2018​	Team3​	3​
11​	2018​	Team3​	3​
12​	2018​	Team3​	3​
13​	2019​	Team1​	1​
14​	2019​	Team1​	1​
15​	2019​	Team2​	2​
16​	2019​	Team2​	2​
17​	2019​	Team2​	2​
18​	2019​	Team2​	2​
19​	2019​	Team2​	2​
20​	2019​	Team3​	3​
21​	2019​	Team3​	3​
22​	2019​	Team3​	3​
23​	2019​	Team3​	3​

<tbody>
</tbody>

 

[Joe4]

How about:

[COLOR=#333333]=IF(A1<>A2,1,IF(B1=B2,C1,MAX($C$1:C1)+1))[/COLOR]

 

[FryGirl]

Hi Joe and thanks. That we my first thought, but that only changes the first grouping after the new year. After that the max is used.

Data Range

	A​	B​	C​
1​	Year​	Team​	Series​
2​	2018​	Team1​	1​
3​	2018​	Team1​	1​
4​	2018​	Team1​	1​
5​	2018​	Team2​	2​
6​	2018​	Team2​	2​
7​	2018​	Team2​	2​
8​	2018​	Team2​	2​
9​	2018​	Team3​	3​
10​	2018​	Team3​	3​
11​	2018​	Team3​	3​
12​	2018​	Team1​	4​
13​	2018​	Team1​	4​
14​	2018​	Team1​	4​
15​	2019​	Team1​	1​
16​	2019​	Team1​	1​
17​	2019​	Team2​	5​
18​	2019​	Team2​	5​
19​	2019​	Team2​	5​
20​	2019​	Team2​	5​
21​	2019​	Team2​	5​
22​	2019​	Team3​	6​
23​	2019​	Team3​	6​
24​	2019​	Team3​	6​
25​	2019​	Team3​	6​

 

[Joe4]

Actually, you do not need to use MAX at all. Since they are only ever increasing by 1 based on the previous value, just use:

=IF(A1<>A2,1,IF(B1=B2,C1,C1+1))

 

[FryGirl]

Perfect. That does it, thanks.

 

[Joe4]

You are welcome.

 

[VBA learner ITG]

This is amazing

 

[VBA learner ITG]

Is it possible to amend this formula to restart after 20 rows?

 

[VBA learner ITG]

Actually, you do not need to use MAX at all. Since they are only ever increasing by 1 based on the previous value, just use:

[COLOR=#333333]=IF(A1<>A2,1,IF(B1=B2,C1,C1+1))[/COLOR]

Is it possible to get this formula to restart after 20 rows

 

[Joe4]

Is it possible to get this formula to restart after 20 rows

That really complicates it a bit, and really is a question deserving of its own thread (instead of tacking on to an old, existing one posted by someone else).
You will get more people looking at it too, if it is in a new, unanswered thread.

Be sure to be detailed in your question, and include screen prints of the data, so people can see exactly what you are working with and what you would like to happen.