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

highlighting duplicates
 

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

highlighting duplicates
 

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" wrote in message
oups.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

highlighting duplicates
 

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

highlighting duplicates
 

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 ?

highlighting duplicates
 

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

Will be a ready resource.

--
Regards,
Tom Ogilvy


"Lynn" wrote in message
oups.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 ?

highlighting duplicates
 

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

highlighting duplicates
 

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

highlighting duplicates
 

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

--
Regards,
Tom Ogilvy


"Lynn" wrote in message
oups.com...
sorry what do you mean?
do you mean that storing Countif($A:$A,A1)1 readily for use is not
possible?

highlighting duplicates
 

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

highlighting duplicates
 

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" wrote in message
oups.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?