Highlight only the first duplicate number that found in a column

[onin18]

Kindly please help me, I have sheet 1 and in

c3	d3
1st	copy
1347	0934
1258	1258
8753	2367
9032	3789
1258	5934
4356	6367
1258	7589
8901	9378

<tbody>
</tbody>

this two column compare for duplicates and I manage to highlight (actually not bold) the number that is duplicate but I dont want to highlight the 2nd, 3rd & so on duplicate number in c3 (highlight 1 only from the 3 numbers most of the times got 2 only) . (row start from c3 to c2000 and d3 is up to d2000 also). I already use CF for highlighting the duplicate in C3 which my formula in CF is

=IF(ISERROR(MATCH(c3:c3,$c$3:$c$780,0)),"",c3:c3) I just try to do this formula and it works, but I dont know how to command not to highlight the 2nd, 3rd & so on duplicate number in C3 (C3 only can have duplicate number within the column; D3 doesn't have any repeat number in the D column.

I need to do the same formula command for other 2 partner columns (compare for duplicates this two columns & the 1st column if have to many duplicates highlight the first number that have more than 1 duplicates only) cells need to do again is for E3 & F3; G3&H3; until AU3&AV3..

Kindly please try to help me, I cant finish my work and my brain want to burst already (didn't study this but trying to learn from here...

Hope to hear from you all soon.. Thx

P.s.

is it possible to count how many highlighted cell (not bold) there is in a column (I mean total highlighted cell) and put the answer in cell C1, E1, G1, I1 ... AS1 , AU1. Tq again..

 

[Trevor G]

Part reply this User Defined Function (UDF) will count coloured cells, you would place it in VBA (Alt + F11) then Insert Menu and Modlue copy the code, then in Worksheet Select an empty cell and then use the Function Guide and change the category to User Defined and select the Function name and then select the range of cells and then in the criteria select the cell that has the colour you want and it should give you a count.

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Font.ColorIndex
For Each datax In range_data
If datax.Font.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

 

[Trevor G]

This Conditional Format formula will highlight the first duplicate

[B]Sheet2[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:64px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]Big[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD]Hat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD]Small[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD]Next[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD]Hat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD]Jumper[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD][B]Conditional formatting [/B][/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Nr.: / Condition[/TD]
[TD]Format[/TD]
[/TR]
[TR]
[TD]H3[/TD]
[TD]1. / Formula is =AND(COUNTIF($H$3:H3,H3)=1,COUNTIF($H$3:$H$2000,H3)>1)[/TD]
[TD="bgcolor: #000000"]Abc[/TD]
[/TR]
[TR]
[TD]H4[/TD]
[TD]1. / Formula is =AND(COUNTIF($H$3:H3,H3)=1,COUNTIF($H$3:$H$2000,H3)>1)[/TD]
[TD="bgcolor: #000000"]Abc[/TD]
[/TR]
[TR]
[TD]H5[/TD]
[TD]1. / Formula is =AND(COUNTIF($H$3:H3,H3)=1,COUNTIF($H$3:$H$2000,H3)>1)[/TD]
[TD="bgcolor: #000000"]Abc[/TD]
[/TR]
[TR]
[TD]H6[/TD]
[TD]1. / Formula is =AND(COUNTIF($H$3:H3,H3)=1,COUNTIF($H$3:$H$2000,H3)>1)[/TD]
[TD="bgcolor: #000000"]Abc[/TD]
[/TR]
[TR]
[TD]H7[/TD]
[TD]1. / Formula is =AND(COUNTIF($H$3:H3,H3)=1,COUNTIF($H$3:$H$2000,H3)>1)[/TD]
[TD="bgcolor: #000000"]Abc[/TD]
[/TR]
[TR]
[TD]H8[/TD]
[TD]1. / Formula is =AND(COUNTIF($H$3:H3,H3)=1,COUNTIF($H$3:$H$2000,H3)>1)[/TD]
[TD="bgcolor: #000000"]Abc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.excel-jeanie-html.de/index.php?f=1"] Excel Jeanie HTML 4 [/URL]

 

[alanga]

Crossposted

http://http://www.ozgrid.com/forum/showthread.php?t=182064

 

[alanga]

Correcting link to crosspost

Highlight only the first duplicate number that found in a column