Add colour to first duplicate and select

[sebastico]

Hi Xls Community.

I have 1826 cells with duplicates. I used the formula
=COUNTIF($A$2:$A$1826,A2)=1 to identify the all duplicates.

I would like xls to add colour only to the first duplicated and select all
coloured cells to copy and given the choice to paste in another column. Your
help is welcome.
Thanks

 

[Luke M]

First, let's change your formula around to only identify the first duplicate:
=IF(AND(COUNTIF($A$2:$A$1826,A2)>1,COUNTIF($A$2:$A2,A2)=1),A2)

Under Format-Conditional Format, setttings are "Cell Value is:", not equal
to, FALSE
Pick desired color.

Then, to select only those cells, select entire column of formula, and press
Ctrl+g. Click special, and then choose formula, with only text and numbers
checked.

You can now copy these cells, and do a paste special - values only into a
column of choice.

Note that it's possible to record those last couple of steps as a macro if
you wish to somehow automate the process for future usues.

 

[Bernard Liengme]

Maybe, the OP wanted the first <duplicate> value highlighted (not the first
value)
then he could use
=IF(AND(COUNTIF($A$2:$A$1826,A2)>1,COUNTIF($A$2:$A2,A2)=2),A2)
best wishes