highlighting duplicates

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

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



Lynn wrote:

>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
>
>
>

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

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.

--
Regards,
Tom Ogilvy



"Lynn" <moley_cruz@yahoo.com.au> wrote in message
news:1129032607.809366.316190@g44g2000cwa.googlegroups.com...
> 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
>

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 ?

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

Will be a ready resource.

--
Regards,
Tom Ogilvy


"Lynn" <moley_cruz@yahoo.com.au> wrote in message
news:1129034579.447163.132050@f14g2000cwb.googlegroups.com...
> 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 ?
>

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

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

--
Regards,
Tom Ogilvy


"Lynn" <moley_cruz@yahoo.com.au> wrote in message
news:1129035697.474077.115690@z14g2000cwz.googlegroups.com...
> sorry what do you mean?
> do you mean that storing Countif($A:$A,A1)>1 readily for use is not
> possible?
>

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