How do I create formula to identify duplicate cells?

Using Excel 2000, I need to identify rows that have matching cells. For
example, B3=B4 and C3=C4. I have used the and/if function in the past and
been able to add something to show me what was duplicated but it has been so
long I can't remember how. I have a delete macro but I need to see what is
duplicated instead of just getting rid of the duplicates.

Hi Kelly

something along the lines of
=IF(AND(B3=B4,C3=C4),"delete","don't delete")

Cheers
JulieD

"Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> Using Excel 2000, I need to identify rows that have matching cells. For
> example, B3=B4 and C3=C4. I have used the and/if function in the past and
> been able to add something to show me what was duplicated but it has been
> so
> long I can't remember how. I have a delete macro but I need to see what
> is
> duplicated instead of just getting rid of the duplicates.

If the duplicates don't have to be contiguous, use

=IF(AND(COUNTIF(B:B,B3)>1,COUNTIF(C:C,C3)>1),"delete", "don't delete")

and copy down

this will delete both duplicate lines. If you want to delete subsequent
duplicates then use

=IF(AND(COUNTIF(B$3:B3,B3)>1,COUNTIF(C$3:C3,C3)>1),"delete", "don't delete")

and then copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:eq51C6xJFHA.732@TK2MSFTNGP12.phx.gbl...
> Hi Kelly
>
> something along the lines of
> =IF(AND(B3=B4,C3=C4),"delete","don't delete")
>
> Cheers
> JulieD
>
> "Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
> news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> > Using Excel 2000, I need to identify rows that have matching cells. For
> > example, B3=B4 and C3=C4. I have used the and/if function in the past
and
> > been able to add something to show me what was duplicated but it has
been
> > so
> > long I can't remember how. I have a delete macro but I need to see what
> > is
> > duplicated instead of just getting rid of the duplicates.
>
>