Google Sheets auto increment column B with empty cells restarting from 1 at each new category String in Column A instead of continuous incrementing

Question

I found this partial solution to my problem:

Google Sheets auto increment column A if column B is not empty

With this formula:

=ARRAYFORMULA(IFERROR(MATCH($B$2:$B&ROW($B$2:$B),FILTER($B$2:$B&ROW($B$2:$B),$B$2:$B<>""),0)))

What I need is the same but instead of continuous numbers I'd need it to restart incrementing from 1 at each new category string on an adjacent column (column A in example below, categories strings are A, B, C, D etc.).

For example:

Problem with formula in C12 and C15 (added numbers 1 and 2)

Needed result in column D, as with D11 and D19 restarts incrementing from 1 at new category string)

1				needed result
2	A	1	1	1
3	A
4	A
5	A	1	2	2
6	A
7	A
8	A
9	A	1	3	3
10	A
11	B	1	4	1
12	B		1
13	B
14	C	1	5	2
15	C		2
16	C
17	C	1	6	3
18	C
19	D	1	7	1
20	D
21	D
22	D	1	8	2
23	D
24	D	1	9	3
25	D
26	D
27	D	1	10	4
28	D
29	D

score 3 · Accepted Answer · answered Nov 15 '22 at 14:24

3

try:

=INDEX(IF(B2:B="",,COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A))))

or:

=INDEX(IF(B2:B="",,COUNTIFS(A2:A&IF(B2:B<>"", 1, ), A2:A&IF(B2:B<>"", 1, ), ROW(A2:A), "<="&ROW(A2:A))))

answered Nov 15 '22 at 14:24

player0

124,011
12
67
124

Thanks @player0 that too worked like a charm! I'll study it next. be well! – Lod Nov 15 '22 at 14:39

score 2 · Answer 2 · answered Nov 15 '22 at 14:32

2

Here's another similar solution.

=ArrayFormula(if(B2:B="",,countifs(A2:A,A2:A,B2:B,"<>",row(A2:A),"<="&row(A2:A))))

answered Nov 15 '22 at 14:32

z''

4,527
2
3
12

Thanks @ztiaa that too worked like a charm! I'll study it next. be well! – Lod Nov 15 '22 at 14:40

1				needed result
2	A	1	1	1
3	A
4	A
5	A	1	2	2
6	A
7	A
8	A
9	A	1	3	3
10	A
11	B	1	4	1
12	B		1
13	B
14	C	1	5	2
15	C		2
16	C
17	C	1	6	3
18	C
19	D	1	7	1
20	D
21	D
22	D	1	8	2
23	D
24	D	1	9	3
25	D
26	D
27	D	1	10	4
28	D
29	D

1				needed result
2	A	1	1	1
3	A
4	A
5	A	1	2	2
6	A
7	A
8	A
9	A	1	3	3
10	A
11	B	1	4	1
12	B		1
13	B
14	C	1	5	2
15	C		2
16	C
17	C	1	6	3
18	C
19	D	1	7	1
20	D
21	D
22	D	1	8	2
23	D
24	D	1	9	3
25	D
26	D
27	D	1	10	4
28	D
29	D

Google Sheets auto increment column B with empty cells restarting from 1 at each new category String in Column A instead of continuous incrementing

2 Answers2

1				needed result
2	A	1	1	1
3	A
4	A
5	A	1	2	2
6	A
7	A
8	A
9	A	1	3	3
10	A
11	B	1	4	1
12	B		1
13	B
14	C	1	5	2
15	C		2
16	C
17	C	1	6	3
18	C
19	D	1	7	1
20	D
21	D
22	D	1	8	2
23	D
24	D	1	9	3
25	D
26	D
27	D	1	10	4
28	D
29	D