Find duplicates in column referencing a 2nd column

[siggyr1]

Hi

I'm trying to highlight duplicated numerical data in column P using Conditional Formatting =COUNTIF(P:P,P2)>1.

However, I only want the duplicate data to be highlighted if the data in the next column Q = "expired". (same row)

I'm trying derivatives of ==COUNTIF(P:P,P3)>1,and(Q3="current"), not having much success.

Any thoughts anyone?

Many thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

There are a couple of ways to interpret what you have asked. To clarify, would P2 be formatted in this example since Q2 is "expired" and P2 is duplicated? Or would Q4 also need to be "expired" and then P2 and P4 would be formatted?

Also, what version of Excel are you using?

Excel Workbook
	P	Q
1
2	1	expired
3	2
4	1
5
CF

 

[siggyr1]

Hi

All fields in Q would have an entry of either "Current" or "Expired".
My CF should only show those entries in P where the corresponding Q in the same row is "Expired"

Using 2007 SP2

Thank you.

 

[Warship]

=AND(COUNTIF(P:P,P3)>1,Q3="expired")

 

[Peter_SSs]

Hi

All fields in Q would have an entry of either "Current" or "Expired".
My CF should only show those entries in P where the corresponding Q in the same row is "Expired"

Using 2007 SP2

Thank you.

That still doesn't clarify which column(s) have to be duplicated. So basically same question as before:

Would P2 be formatted in this example since Q2 is "expired" and P2 is duplicated? Or would Q4 also need to be "expired" and then P2 and P4 would be formatted?

Excel Workbook
	P	Q
1
2	1	expired
3	2	expired
4	1	current
5
CF

 

[siggyr1]

Sorry, I see the question now.

I'm only interested to see where P column has duplicate data in it, where same row Q = "expired".
i.e. P2 and P4 are the same, and Q2 & Q4 = "expired"

I'm not concerned if P2 is duplicated with P4, but Q2 = "current" and Q4 = "expired" .

Hope that makes sense..??

 

[Peter_SSs]

Select P2:Pxx and apply the Conditional Formatting shown. You may need to adjust the $100 in the formula if your data goes beyond row 100 or if it is significantly less than 100.

Excel Workbook
	P	Q
1
2	1	expired
3	2	expired
4	1	current
5	1	expired
6	3	expired
7	3	expired
8
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2	1. / Formula is =AND(Q2="expired",COUNTIFS($P$2:$P$100,P2,$Q$2:$Q$100,"expired")>1)	Abc

 

[siggyr1]

Hi

This has nailed it.

Thanks so much for your help....!!