Dynamic Row Counting with duplicate data

[Wrekin]

Hi All,

I have a query relating to a formula that has been giving me a problem.

What I need is the column 'Review Number' to return a repeatable sequential count based upon the Date (Col B) and ID Number (Col D) entries, in other words the first ID 1234 (never to be duplicated again) has four entries, is the first ID Number checked and is therefore the Review Number (Col C) should return '1'. Likewise the second ID 5678 has three entries, is the second ID Number checked and is therefore Review Number '2' and so on upto Review Number '5', and a new Date is reached here the whole process of numbering from 1 should start again.

In column A I have the formula =countif($B$4:$B4,B4) this returns a sequential number which starts at 1 again once ROW 20 is reach (change of Date). I tried to adopt the same type of formula but just can't get it to work.

So please can anybody help with the formula in 'Review Number' Col B, TIA.

 

[Eric W]

Welcome to the MrExcel forum!

Try this in C3 and drag down:

=IF(AND(B3=B2,D3=D2),C2,SUM(C2,1))

 

[Wrekin]

Welcome to the MrExcel forum!

Try this in C3 and drag down:

=IF(AND(B3=B2,D3=D2),C2,SUM(C2,1))

Thanks for the input Eric, the formula partly works. The remaining issue is that the 'Review Number' needs to start at '1' again once the Date changes in Col B (Pretty much how the formula works in Col A).

New uploaded image shows your formula in Col C, working perfect upto Row 20.

Formulas shown in Col F & G, for info. Col E is the original 'Review Number' as text.

 

[Eric W]

Try this:

Book1 (version 1).xlsb
	A	B	C	D	E
2	Date Sequence Number	Date	Review Number	ID Number	Original Example Number
3
4	1	12/1/2021	1	1234	1
5	2	12/1/2021	1	1234	1
6	3	12/1/2021	1	1234	1
7	4	12/1/2021	1	1234	1
8	5	12/1/2021	2	5678	2
9	6	12/1/2021	2	5678	2
10	7	12/1/2021	2	5678	2
11	8	12/1/2021	3	ABCD	3
12	9	12/1/2021	3	ABCD	3
13	10	12/1/2021	3	ABCD	3
14	11	12/1/2021	3	ABCD	3
15	12	12/1/2021	4	EFGH	4
16	13	12/1/2021	4	EFGH	4
17	14	12/1/2021	4	EFGH	4
18	15	12/1/2021	5	IJKL	5
19	16	12/1/2021	5	IJKL	5
20	1	1/1/2022	1	A4321	1
21	2	1/1/2022	1	A4321	1
22	3	1/1/2022	1	A4321	1
23	4	1/1/2022	1	A4321	1
24	5	1/1/2022	2	B5678	2
25	6	1/1/2022	2	B5678	2
26	7	1/1/2022	3	C9ABC	3
27	8	1/1/2022	3	C9ABC	3
28	9	1/1/2022	3	C9ABC	3
29	10	1/1/2022	4	DEFGH9	4
Sheet10

Cell Formulas
Range	Formula
C4:C29	C4	=IF(B4<>B3,1,IF(D4<>D3,SUM(C3,1),C3))

 

[Wrekin]

Try this:

Book1 (version 1).xlsb
	A	B	C	D	E
2	Date Sequence Number	Date	Review Number	ID Number	Original Example Number
3
4	1	12/1/2021	1	1234	1
5	2	12/1/2021	1	1234	1
6	3	12/1/2021	1	1234	1
7	4	12/1/2021	1	1234	1
8	5	12/1/2021	2	5678	2
9	6	12/1/2021	2	5678	2
10	7	12/1/2021	2	5678	2
11	8	12/1/2021	3	ABCD	3
12	9	12/1/2021	3	ABCD	3
13	10	12/1/2021	3	ABCD	3
14	11	12/1/2021	3	ABCD	3
15	12	12/1/2021	4	EFGH	4
16	13	12/1/2021	4	EFGH	4
17	14	12/1/2021	4	EFGH	4
18	15	12/1/2021	5	IJKL	5
19	16	12/1/2021	5	IJKL	5
20	1	1/1/2022	1	A4321	1
21	2	1/1/2022	1	A4321	1
22	3	1/1/2022	1	A4321	1
23	4	1/1/2022	1	A4321	1
24	5	1/1/2022	2	B5678	2
25	6	1/1/2022	2	B5678	2
26	7	1/1/2022	3	C9ABC	3
27	8	1/1/2022	3	C9ABC	3
28	9	1/1/2022	3	C9ABC	3
29	10	1/1/2022	4	DEFGH9	4
Sheet10

Cell Formulas
Range	Formula
C4:C29	C4	=IF(B4<>B3,1,IF(D4<>D3,SUM(C3,1),C3))

Eric, you are a super star many thanks

 

[Eric W]

Glad to help! Thanks for the follow-up. ?