Thread: 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" <lbrew@discussions.microsoft.com> wrote in message
news:1C2E3484-532C-4D8F-B170-B6A93C8DAFA9@microsoft.com...
> 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.
>
>