duplicate words

[Guest]

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.

 

[Bob Phillips]

Try this

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

--

HTH

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

 

[Guest]

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)