Conditional Formatting for two duplicates

[kalcherd]

Really struggle with Excel, so this might be a quick and easy one for someone out there?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I want to conditional format a worksheet, which is to check for two criteria’s in two columns to capture duplications and then colour that row in the worksheet. <o></o>
<o> </o>
I can make this happen for the first check using countif to see if two numbers of the same value have been entered into column G =COUNTIF($G$2:$G$1009,$G2)>1. However, the second critera I just cannot get to work as one with 1<SUP>st</SUP>, as one countif statement.<o></o>
<o></o>
The second duplication check, is on column F which has an option of two text entries, which come from a data validation list. The two text options are FBR and TPT which has meaning to a vlookup I'm doing in column H based on the number entered in column G, returning the vlookup for the respective tabs of FBR and TPT.<o></o>
<o></o>
So I want to flag when number 1 in column G is already in the list and being used for vlookup for one tab, either FBR or TPT. Therefore I can have a number 1 as a vlookup for FBR tab & one for TPT tab, and this is not a duplicate. It's just when it the number has been entered twice for the same TAB, say FBR.<o></o>
<o></o>
Can someone help? <o></o>

 

[Peter_SSs]

Welcome to the MrExcel board!

If this is not what you want, please post back with more details and which Excel version you are using.

If Excel 2007+, select A2 to bottom right of data (A2:H10 for me) and apply the CF shown.

Excel Workbook
	A	B	C	D	E	F	G	H
1	H1	H2	H3	H4	H5	H6	H7	H8
2						FBR	1
3						TPT	2
4						TPT	3
5							2
6							1
7						TPT	2
8						FBR	3
9						FBR	1
10							4
11
CF 2007+

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =COUNTIFS($G$2:$G$1010,$G2,$F$2:$F$1010,$F2)>1	Abc

If Excel 2003-, then

Excel Workbook
	A	B	C	D	E	F	G	H
1	H1	H2	H3	H4	H5	H6	H7	H8
2						FBR	1
3						TPT	2
4						TPT	3
5							2
6							1
7						TPT	2
8						FBR	3
9						FBR	1
10							4
11
CF 2003-

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =SUMPRODUCT(--($G$2:$G$1009=$G2),--($F$2:$F$1009=$F2),--($G$2:$G$1009<>""),--($F$2:$F$1009<>""))>1	Abc

 

[kalcherd]

Peter, You have cracked this for me.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
What a great site this is and everyone is so lucky to have a mediator like you.<o></o>
Many thanks,<o></o>
DK<o></o>