Add colour to first duplicate and select
 

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

Add colour to first duplicate and select
 

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:$A 2,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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"sebastico" wrote:

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

Add colour to first duplicate and select
 

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:$A 2,A2)=2),A2)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Luke M" wrote in message
...
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:$A 2,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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"sebastico" wrote:

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