duplicate words
 

If each cell in 3 columns has mupltiple words and numbers - Does excel have
the ability to to look at say 3 rows and 3 columns and identify the duplicate
words or numbers? Either by idenitying the common cells individually
depending on how I format or looking at a range of mutiple columns and rows
and tell me there are duplicates (then I know where to look)?

example:
column a column b column c
bp oil 1234 hess 4321 sheetz 568
hess 4321 shell 9876 shell 1234
bp oil 7890 bp oil 1234 sheetz 568

I used a range and conditional formatting
=IF(COUNTIF(range1,A4)1,TRUE,FALSE) but the cells have to be exact. If BP
Oil 1234 has 2 spaces in between Oil and 1234 then it won't highlight that
particular one.

Try this

=IF(SUMPRODUCT(--(SUBSTITUTE(range1," ","")=SUBSTITUTE(A4,"
","")))1,TRUE,FALSE)

--

HTH

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


"lbrew" wrote in message
...
If each cell in 3 columns has mupltiple words and numbers - Does excel
have
the ability to to look at say 3 rows and 3 columns and identify the
duplicate
words or numbers? Either by idenitying the common cells individually
depending on how I format or looking at a range of mutiple columns and
rows
and tell me there are duplicates (then I know where to look)?

example:
column a column b column c
bp oil 1234 hess 4321 sheetz 568
hess 4321 shell 9876 shell 1234
bp oil 7890 bp oil 1234 sheetz 568

I used a range and conditional formatting
=IF(COUNTIF(range1,A4)1,TRUE,FALSE) but the cells have to be exact. If
BP
Oil 1234 has 2 spaces in between Oil and 1234 then it won't highlight that
particular one.

Ibrew -
Use the TRIM(...) function. It removes all spaces from a string except for
a single space between the words. So update your function to be:

=IF(SUMPRODUCT(--(TRIM(range1)=TRIM(A4)))1,TRUE,FALSE)

---
Regards,
Dave


"lbrew" wrote:

If each cell in 3 columns has mupltiple words and numbers - Does excel have
the ability to to look at say 3 rows and 3 columns and identify the duplicate
words or numbers? Either by idenitying the common cells individually
depending on how I format or looking at a range of mutiple columns and rows
and tell me there are duplicates (then I know where to look)?

example:
column a column b column c
bp oil 1234 hess 4321 sheetz 568
hess 4321 shell 9876 shell 1234
bp oil 7890 bp oil 1234 sheetz 568

I used a range and conditional formatting
=IF(COUNTIF(range1,A4)1,TRUE,FALSE) but the cells have to be exact. If BP
Oil 1234 has 2 spaces in between Oil and 1234 then it won't highlight that
particular one.