How do I create formula to identify duplicate cells?

[Kelly Lacey]

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.

 

[JulieD]

Hi Kelly

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

Cheers
JulieD

 

[Bob Phillips]

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)

 

[Andrewac]

I never like the solutions offered which delete or hide duplicates - how many
were there? You loose visibility. Why this is not a standard Excel operator
I don't know.

This is another way I worked out a while ago, not as elegant as Bob's but
shows another function:

In this case we want to mark duplicate cells from C8 to C999.

In a next door column enter this or something logically similar in row 8:

=OR(NOT(ISNA(MATCH(C8,C9:C$999,FALSE))),NOT(ISNA(MATCH(C8,C$7:C10,FALSE))))

Where the ISNA function checks for repeated values within a range, this does
the ISNA check above and then below the current value line. Then returns the
OR of the answer. The NOT function is because the OR is looknig for TRUE
values.

Note the ranges start 1 below and 1 above the current row. Copy the formula
down in the usual way.

This does work, make sure you fix the correct values with the $ sign.

 

[KTomk]

This is a great solution, thanks for posting it! I had over 7,700 rows to
examine. Using conditional formatting to hightlight the "TRUE" occurences
made it even easier to spot the dupes.

 

[JJH.]

A much simpler method is to insert a new column (B) alongside the duplicated
data and enter this formula in each cell of column B: =IF(A2=A3,"DUP","").
Then you can sort or filter on column B to read or delete the duplicated
range as a whole.

 

[Krish]

"Krish"
Thanks you very much, it did solve my problem.

 

[Mirella]

THIS is the solution i was looking for...THANK YOU!