Flag only if there are duplicate values and matching criteria in another column

[Dokat]

Hi

I am trying the flag "Ration" in the output column based on 2 criteria,

1 Only duplicate values in CCP Name.

2. Corresponding Stock Type for duplicate values has to be 'Inactive - Use Up Inventory" . In below ex. i put the ration where there is duplicate values in CCP Name column and Stock type for both duplicate values is "Inactive-Use Up Inventory". Does anyone had a similar issue and formula that can solve it?

CCP Name	Stock Type	Output
CSA11-BLK​	Active - Use Up Inventory
CSA11-BLU​	Inactive - Use Up Inventory	Ration​
CSAP51-BLK​	Inactive - Use Up Inventory
CSAP51-BLU​	Inactive - Use Up Inventory
CSAP51CS-BLK​	Inactive - Use Up Inventory
CSA11-BLU​	Inactive - Use Up Inventory	Ration​

 

[Peter_SSs]

Like this?

20 11 20.xlsm
	A	B	C
1	CCP Name	Stock Type	Output
2	CSA11-BLK	Active - Use Up Inventory
3	CSA11-BLU	Inactive - Use Up Inventory	Ration
4	CSAP51-BLK	Inactive - Use Up Inventory
5	CSAP51-BLU	Inactive - Use Up Inventory
6	CSAP51CS-BLK	Inactive - Use Up Inventory
7	CSA11-BLU	Inactive - Use Up Inventory	Ration
Ration

Cell Formulas
Range	Formula
C2:C7	C2	=IF(AND(COUNTIF(A$2:A$7,A2)>1,B2="Inactive - Use Up Inventory"),"Ration","")

 

[Dokat]

Thank you it worked

 

[Dokat]

Like this?

One more question formula works fine when all stock types are "Inactive - Use Up Inventory" however it doesn't work if one of them is "Inactive - Use Up Inventory" and the other is "Active - Use Up Inventory" Is there any way it only flags where both conditions meets for both columuns.

20 11 20.xlsm
	A	B	C
1	CCP Name	Stock Type	Output
2	CSA11-BLK	Active - Use Up Inventory
3	CSA11-BLU	Inactive - Use Up Inventory	Ration
4	CSAP51-BLK	Inactive - Use Up Inventory
5	CSAP51-BLU	Inactive - Use Up Inventory
6	CSAP51CS-BLK	Inactive - Use Up Inventory
7	CSA11-BLU	Inactive - Use Up Inventory	Ration
Ration

Cell Formulas
Range	Formula
C2:C7	C2	=IF(AND(COUNTIF(A$2:A$7,A2)>1,B2="Inactive - Use Up Inventory"),"Ration","")

 

[Peter_SSs]

Is there any way it only flags where both conditions meets for both columuns.

OK, there wasn't one like that in the samples.
Try

=IF(COUNTIFS(A$2:A$7,A2,B$2:B$7,"Inactive - Use Up Inventory")>1,"Ration","")

 

[Dokat]

OK, there wasn't one like that in the samples.
Try

=IF(COUNTIFS(A$2:A$7,A2,B$2:B$7,"Inactive - Use Up Inventory")>1,"Ration","")

This works when there is only 2 rows of duplicate data however doesn't work when there is 3 or more. Please see below.
In the below example, CSA11-BLK shouldn't be flagged as Ration as one of the corresponding values under Stock Type is Active-Use Up Inventory. Is there any way to make the formula work when both conditions meet and apply to all rows?

CCP Name	Stock Type	Output
CSA11-BLK​	Inactive - Use Up Inventory	Ration
CSA11-BLU​	Inactive - Use Up Inventory	Ration
CSA11-BLK​	Active - Use Up Inventory	Ration
CSA11-BLK​	Inactive - Use Up Inventory	Ration
CSAP51CS-BLK​	Inactive - Use Up Inventory
CSA11-BLU​	Inactive - Use Up Inventory	Ration

 

[Peter_SSs]

Oops, missed one check, sorry. Try again.

20 11 20.xlsm
	A	B	C
1	CCP Name	Stock Type	Output
2	CSA11-BLK	Inactive - Use Up Inventory	Ration
3	CSA11-BLU	Inactive - Use Up Inventory	Ration
4	CSA11-BLK	Active - Use Up Inventory
5	CSA11-BLK	Inactive - Use Up Inventory	Ration
6	CSAP51CS-BLK	Inactive - Use Up Inventory
7	CSA11-BLU	Inactive - Use Up Inventory	Ration
Ration (3)

Cell Formulas
Range	Formula
C2:C7	C2	=IF(AND(B2="Inactive - Use Up Inventory",COUNTIFS(A$2:A$7,A2,B$2:B$7,"Inactive - Use Up Inventory")>1),"Ration","")