Formula or Code to highlight when only 2 matching in cell

[Dazzawm]

I have column C laid out as below. I would like a formula or code please to highlight when there are only 2 that match. As you can see in example I have highlighted in yellow how the results would look. Thanks

Excel 2010
	C
9228	FTGRAP13D 1001
9229	FTGRAP13D 1001
9230	FTGRAP13D 1004
9231	FTGRAP13D 1004
9232	FTGRAP13D 1004
9233	FTGRAP14 1001
9234	FTGRAP14 1001
9235	FTGRAP14 1001
9236	FTGRAP14 1004
9237	FTGRAP14 1004
Sheet2

 

[Fluff]

Select the cells in col C & with conditional formatting, use this formula

=COUNTIF(C$2:C$1000,C2)=2
Change the start & end rows to suit

 

[Dazzawm]

Thanks, is there a way without conditional formatting? It is a massive sheet of 100,000 rows and it just keeps freezing when trying to sort or if I delete rows it recalculates again and freezes again....

I've also noticed it doesn't give the required results.

 

[Fluff]

You could put that formula in col D & copy down, it will then show either TRUE or FALSE.

 

[Dazzawm]

Thats better thanks.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Dazzawm]

Hi I am amending this formula to allow 3 rather than 2, but it returns False on all?

 

[Fluff]

How have you amended it?

 

[Dazzawm]

Changed the 2 to 3 on end

 

[Fluff]

That should work

Excel 2013/2016
	B	C	D
2	9228	FTGRAP13D 1001	FALSE
3	9229	FTGRAP13D 1001	FALSE
4	9230	FTGRAP13D 1004	TRUE
5	9231	FTGRAP13D 1004	TRUE
6	9232	FTGRAP13D 1004	TRUE
7	9233	FTGRAP14 1001	TRUE
8	9234	FTGRAP14 1001	TRUE
9	9235	FTGRAP14 1001	TRUE
10	9236	FTGRAP14 1004	FALSE
11	9237	FTGRAP14 1004	FALSE
List

Cell Formulas
Range	Formula
D2	=COUNTIF(C$2:C$1000,C2)=3