How do I check whether the same string value appears within a range of cells

e.g.

A
1 JD
2 DF
3 AM
4 FC
5 RS
6 DF
7 JD

How do I get excel to check down column A1:A7 to see if any of the different
strings are repeated? In this example I want to return the answer "yes"
because JD appears in both A1 and A7. However, if this wasn't the case then I
would want excel to return the answer "no"

I am using excel 2007

Many thanks

JD

In B1, or another available cell on row 1, enter this formula:
=IF(COUNTIF(A$1:A$7,A1)1,"YES","NO")
Fill the formula down to the end of the list in column A. Change the A$7
part to use the last row number for your list in column A.
Duplicate/multiple entries will be identified as "YES" until you remove all
but 1 of them for all entries. That is, you will get YES at row 1 and 7
until one of the JD entries is removed.

"JRD" wrote:

How do I check whether the same string value appears within a range of cells

e.g.

A
1 JD
2 DF
3 AM
4 FC
5 RS
6 DF
7 JD

How do I get excel to check down column A1:A7 to see if any of the different
strings are repeated? In this example I want to return the answer "yes"
because JD appears in both A1 and A7. However, if this wasn't the case then I
would want excel to return the answer "no"

I am using excel 2007

Many thanks

JD

Assuming there are no empty cells within in the range:

=IF(SUMPRODUCT(COUNTIF(A1:A7,A1:A7))=ROWS(A1:A7)," Yes","No")

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
How do I check whether the same string value appears within a range of
cells

e.g.

A
1 JD
2 DF
3 AM
4 FC
5 RS
6 DF
7 JD

How do I get excel to check down column A1:A7 to see if any of the
different
strings are repeated? In this example I want to return the answer "yes"
because JD appears in both A1 and A7. However, if this wasn't the case
then I
would want excel to return the answer "no"

I am using excel 2007

Many thanks

JD

=IF(SUMPRODUCT(COUNTIF(A1:A7,A1:A7))=ROWS(A1:A7), "Yes","No")

Ooops! I think I have the Yes/No backwards. So, let's just change the
operator:

=IF(SUMPRODUCT(COUNTIF(A1:A7,A1:A7))ROWS(A1:A7)," Yes","No")

Yes = there are duplicates
No = there are no duplicates


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming there are no empty cells within in the range:

=IF(SUMPRODUCT(COUNTIF(A1:A7,A1:A7))=ROWS(A1:A7)," Yes","No")

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
How do I check whether the same string value appears within a range of
cells

e.g.

A
1 JD
2 DF
3 AM
4 FC
5 RS
6 DF
7 JD

How do I get excel to check down column A1:A7 to see if any of the
different
strings are repeated? In this example I want to return the answer "yes"
because JD appears in both A1 and A7. However, if this wasn't the case
then I
would want excel to return the answer "no"

I am using excel 2007

Many thanks

JD

On Fri, 26 Jun 2009 06:40:01 -0700, JRD wrote:

How do I check whether the same string value appears within a range of cells

e.g.

A
1 JD
2 DF
3 AM
4 FC
5 RS
6 DF
7 JD

How do I get excel to check down column A1:A7 to see if any of the different
strings are repeated? In this example I want to return the answer "yes"
because JD appears in both A1 and A7. However, if this wasn't the case then I
would want excel to return the answer "no"

I am using excel 2007

Many thanks

JD



This formula must be **array-entered**:

=IF(OR(COUNTIF(A1:A7,A1:A7)1),"Yes","No")
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

--ron