highlighting duplicates

[Lynn]

hi,
i have a spreadsheet which contains a serial number on each row. There
are 1000+ rows, does anyone has a macro that can check for rows with
duplicated serial number and highlight them?
thanks

 

[Roger Govier]

Hi Lynn

One way
Set up a helper column and enter
=COUNTIF($A$1:A1000,A1)
Copy down for 1000 rows
Mark the block of data, Data>Filter>Autofilter and use the dropdown on
the helper column to select values >1

Regards

Roger Govier

 

[Tom Ogilvy]

select the entire column with A1 as the active cell (for example).
format => Conditional formatting

change Cell Value is to Formula is in the first dropdown

put in a formula relative to the active cell

=Countif($A:$A,A1)>1

then select the formatting you want to apply when a cell is a duplicate.

 

[Lynn]

can i put this as a marco so that i do not have to remmember this
formula?

 

[Lynn]

thanks Tom,
but still i need to remmember this formula =Countif($A:$A,A1)>1
anyway for me to store it in excel and use it ?

 

[Tom Ogilvy]

http://www.cpearson.com/excel/duplicat.htm

Will be a ready resource.

 

[chris]

Highlight the column that contains the serial number, then goto
data/filter/advanced filter and click the option "unique records only"
it will HIDE all duplicated numbers. If you now select visible cells
only and colour them, unhide all rows, you will be able to see
duplicates

 

[Lynn]

sorry what do you mean?
do you mean that storing Countif($A:$A,A1)>1 readily for use is not
possible?

 

[Tom Ogilvy]

I guess that would depend on what you mean by storing.

 

[Lynn]

storing like i am able to click on a button and the formula will
perform the job.
can this be done in this case?

 

[Tom Ogilvy]

Sure. Turn on the macro recorder while you perform the action manually.
then turn off the macro recorder. Now place a button on the sheet and use
the recorded code as the action performed by the button.

I have provided some links to primer material on macros in answer to a
previous post of yours.