How to Assign Serial Number to Unique Values with Condition in Excel

[rengifot]

I am trying to assign a unique serial number to column C if column A is the same therapeutic class and column b is a different drug so I am looking for a formula that can help me with this task. Column D is the end result, which I did manually. Thank you in advance for your assistance

THERAPEUTIC_CLASS	DRUG_NAME	Assign Unique Serial Number	Sequence
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~ISOTRETINOIN	ABSORICA		1​
ACNE~TOPICAL	ACZONE		1​
ACNE~TOPICAL	ACZONE		1​
ACNE~TOPICAL	AMZEEQ		2​
ACNE~TOPICAL	EPIDUO FORTE		3​
ACNE~TOPICAL	EPIDUO FORTE		3​
ACNE~TOPICAL	ONEXTON		4​
ACNE~TOPICAL	RETIN-A MICRO		5​
ACNE~TOPICAL	RETIN-A MICRO		5​

 

[Candyman8019]

What is the desired format of your unique serial number?
Assuming if column A, B and D are duplicated then the serial number will also be duplicated?

 

[rengifot]

What is the desired format of your unique serial number?
Assuming if column A, B and D are duplicated then the serial number will also be duplicated?

Hi Candyman8019,

Column D is just a reference of what I should get with a formula and it is not part of the criteria. For example for the ACNE~ISOTRETINOIN therapeutic class, I will get only 1 as a serial number because that therapeutic class only has one drug and the same serial number will be applied to the drug's duplicate values.
The next therapeutic class ACNE - TOPICAL has five drugs so the serial number will start again with 1 for the first drug and its duplicates, then 2 for the second drug and its duplicates, then 3 for the third drug and its duplicates, and so on. in conclusion, a new therapeutic class will always start with number 1 for the first drug and its duplicates and then adding +1 to the additional drugs within that therapeutic class. Basically, I am counting all the drugs within a therapeutic class.

Thank you for your assistance.

 

[Candyman8019]

Give this a try:

book5.xlsm
	A	B	C
1	THERAPEUTIC_CLASS	DRUG_NAME	Sequence
2	ACNE~ISOTRETINOIN	ABSORICA	1
3	ACNE~ISOTRETINOIN	ABSORICA	1
4	ACNE~ISOTRETINOIN	ABSORICA	1
5	ACNE~ISOTRETINOIN	ABSORICA	1
6	ACNE~ISOTRETINOIN	ABSORICA	1
7	ACNE~ISOTRETINOIN	ABSORICA	1
8	ACNE~ISOTRETINOIN	ABSORICA	1
9	ACNE~ISOTRETINOIN	ABSORICA	1
10	ACNE~ISOTRETINOIN	ABSORICA	1
11	ACNE~ISOTRETINOIN	ABSORICA	1
12	ACNE~TOPICAL	ACZONE	1
13	ACNE~TOPICAL	ACZONE	1
14	ACNE~TOPICAL	AMZEEQ	2
15	ACNE~TOPICAL	EPIDUO FORTE	3
16	ACNE~TOPICAL	EPIDUO FORTE	3
17	ACNE~TOPICAL	ONEXTON	4
18	ACNE~TOPICAL	RETIN-A MICRO	5
19	ACNE~TOPICAL	RETIN-A MICRO	5
Sheet4

Cell Formulas
Range	Formula
C3:C19	C3	=+IF(A3=A2,IF(B3=B2,C2,IF(A3=A2,C2+1,1)),1)

 

[Candyman8019]

The above is assuming the list is sorted by column A, then Column B.

 

[rengifot]

Thank you very much Candyman8019! The formula worked perfectly for my 50K records! I really appreciate your assistance and expertise.