Add colour to first duplicate and select

  • Thread starter Thread starter sebastico
  • Start date Start date
S

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
 
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.
 
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
 
Back
Top