VBA increment number sequence based on 2 cell values

[NormChart55]

Hello - I am in need of a macro number sequence of files so that I can separate.

I have data headers in column C4-H4 and all the data will be located in C5:H50000 depending on how much data there is (that changes). I am needing to add a number sequence in Column B that goes in increment based on C (address) and H (reference) data. Example below. In the example below, I would want the sequence for first 3 lines to be 1/1/2 because line 3 the reference changes. Lines 4/5 would be 1/2 because the 5th line reference changes. Basically if reference changes compared to the address then it should be a new number sequence for that only so I can filter out the number of separate pages. I hope that makes sense. Thanks for any and all help you can provide.

Address	Data	part	Quantity	vendor	Reference
AAA					XXX
AAA					XXX
AAA					XXY
BBB					YYY
BBB					YYX

 

[kevin9999]

Do you need a "macro" for this?

Book1
	B	C	D	E	F	G	H
4		Address	Data	part	Quantity	vendor	Reference
5	1	AAA					XXX
6	1	AAA					XXX
7	2	AAA					XXY
8	1	BBB					YYY
9	2	BBB					YYX
Sheet1

Cell Formulas
Range	Formula
B5:B9	B5	=IF(C5<>C4,1,IF(AND(C5=C4,H5=H4),1,B4+1))

 

[NormChart55]

=IF(C5<>C4,1,IF(AND(C5=C4,H5=H4),1,B4+1))

Hello - thank you, no I was probably over thinking it. however this equation does not properly sequence as the data may not always be in order. so in this example below, it gives 3 but it should only be 2 since there is only 2 unique numbers for reference?

JGD			​		xxx - 1
JGD			​		xxx - 1
JGD			​		xxy - 2
JGD			​		xxx - 3

 

[NormChart55]

Any thoughts out there how to achieve this even if the first column data is not in order?

 

[Joe4]

This can be done with a simple COUNTIFS formula. Order does not matter.
Place this in cell B5 and copy down:

=COUNTIFS(C$5:C5,C5,H$5:H5,H5)

 

[NormChart55]

This can be done with a simple COUNTIFS formula. Order does not matter.
Place this in cell B5 and copy down:

=COUNTIFS(C$5:C5,C5,H$5:H5,H5)

thanks for the help. that does create a sequence, but if the data in column C and H do not change, it should not increase the sequence. The number sequence should only increase if H changes, and if column C changes the sequence starts again.

 

[Joe4]

I am not sure I understand all your various requirements.
Please post a detailed example that covers all the different scenarios, and show us your expected output.

 

[NormChart55]

I am not sure I understand all your various requirements.
Please post a detailed example that covers all the different scenarios, and show us your expected output.

Below is an example data and includes the Sequence that should come up. thanks for taking a look

Code					Reference	Expected sequence
JYT					XXX	1​
JYT					XXX	1​
JYT					XXY	2​
JGD					XXX	1​
JGD					XXX	1​
JGD					XXY	2​
JGD					XXY	2​
JGD					XXX	1​
JZX					XXX	1​
JZX					XXX	1​
JZX					XXX	1​
JYT					XXX	1​
JYT					XXY	2​
JYT					XXZ	3​

 

[Joe4]

Yeah, that could get a bit tricky.
Any reason why the data cannot be sorted first?
Then you may be able to use one of the early solutions provided.

 

[NormChart55]

I can add a sort if needed, but the other solution still seems to sequence slightly off to what I would expect. if reference changes back to one already used prior, it adds a new number and if the same one is then used going forward it goes back to 1 again. Example below is using this formula

Range	Formula
Cell Formulas
B5:B9	B5	=IF(C5<>C4,1,IF(AND(C5=C4,H5=H4),1,B4+1))

Sequence	CODE					Reference
1​	JGD			​		XXX
1​	JGD			​		XXX
1​	JGD			​		XXX
2​	JGD			​		XXY
3​	JGD			​		XXX
1​	JGD			​		XXX
1​	JGD			​		XXX
1​	JGD			​		XXX
1​	JGD			​		XXX
1​	JGD			​		XXX