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

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
>

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

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

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

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

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

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.

--
Regards,
Tom Ogilvy


"Lynn" <moley_cruz@yahoo.com.au> wrote in message
news:1129036867.013902.172180@g49g2000cwa.googlegroups.com...
> storing like i am able to click on a button and the formula will
> perform the job.
> can this be done in this case?
>