I have a number of cells, each containing a different string value. Is there
a way for me to determine whether a string from position A1 in my sheet,
exists anywhere else in that same column on my sheet.

ie does text from Cell A1 match any text between Cell A2 and Cell A100?

Thanks,
Grant

Hi Grant

one option
=IF(ISNUMBER(MATCH(A1,A2:A100,0)),"found","not found")

or
=IF(COUNTIF(A2:A100,A1)=1,"found","not found")

- or if you want to return the row number of where it is found just use
MATCH(A1,A2:A100,0)+1

Hope this helps
Cheers
JuileD


"Grant" wrote in message
...
I have a number of cells, each containing a different string value. Is
there a way for me to determine whether a string from position A1 in my
sheet, exists anywhere else in that same column on my sheet.

ie does text from Cell A1 match any text between Cell A2 and Cell A100?

Thanks,
Grant

Excellent thanks Juile! Just one question, is there a way to use the countif
formula to search every row from A1 to A100 excluding the current row? Your
formula COUNTIF(A2:A100,A1) works great but when I drag it down the column
it searches from that point on and not any previous cells.

So is there a way to do search every row from A1:A100 except for the current
row?
Something like =COUNTIF( IF ( Current cell not = A1 then use the array(
$A2:$A100 )), A1)

or something similar? I hope Im making sense...


"JulieD" wrote in message
...
Hi Grant

one option
=IF(ISNUMBER(MATCH(A1,A2:A100,0)),"found","not found")

or
=IF(COUNTIF(A2:A100,A1)=1,"found","not found")

- or if you want to return the row number of where it is found just use
MATCH(A1,A2:A100,0)+1

Hope this helps
Cheers
JuileD


"Grant" wrote in message
...
I have a number of cells, each containing a different string value. Is
there a way for me to determine whether a string from position A1 in my
sheet, exists anywhere else in that same column on my sheet.

ie does text from Cell A1 match any text between Cell A2 and Cell A100?

Thanks,
Grant

Grant wrote...
....
So is there a way to do search every row from A1:A100 except for the
current
row?
....

If you mean the formula in, say, A23 would check for instances of the
value of A23 in A1:A22 and A24:A100, then try

=IF(SUMPRODUCT((A$1:A$100=A23)*(ROW(A$1:A$100)<RO W(A23)),"found","not
found")

Alternatively, you could use

A1:
=IF(COUNTIF(A2:A100,A1),"found","not found")

A2 (then filled into A3:A99):
=IF(COUNTIF(A$1:A1,A2)+COUNTIF(A3:A$100,A2),"found ","not found")

A100:
IF(COUNTIF(A1:A99,A100),"found","not found")

Or maybe you could just check to see if the results were 1 (instead of =1).

=if(countif($a$1:$a$100,a1)1,"Found somewhere else besides this cell","Nope")



Grant wrote:

Excellent thanks Juile! Just one question, is there a way to use the countif
formula to search every row from A1 to A100 excluding the current row? Your
formula COUNTIF(A2:A100,A1) works great but when I drag it down the column
it searches from that point on and not any previous cells.

So is there a way to do search every row from A1:A100 except for the current
row?
Something like =COUNTIF( IF ( Current cell not = A1 then use the array(
$A2:$A100 )), A1)

or something similar? I hope Im making sense...

"JulieD" wrote in message
...
Hi Grant

one option
=IF(ISNUMBER(MATCH(A1,A2:A100,0)),"found","not found")

or
=IF(COUNTIF(A2:A100,A1)=1,"found","not found")

- or if you want to return the row number of where it is found just use
MATCH(A1,A2:A100,0)+1

Hope this helps
Cheers
JuileD


"Grant" wrote in message
...
I have a number of cells, each containing a different string value. Is
there a way for me to determine whether a string from position A1 in my
sheet, exists anywhere else in that same column on my sheet.

ie does text from Cell A1 match any text between Cell A2 and Cell A100?

Thanks,
Grant





--

Dave Peterson

Thanks thats perfect! I think I remember trying something similar but
couldnt get it to work.

"Dave Peterson" wrote in message
...
Or maybe you could just check to see if the results were 1 (instead of
=1).

=if(countif($a$1:$a$100,a1)1,"Found somewhere else besides this
cell","Nope")



Grant wrote:

Excellent thanks Juile! Just one question, is there a way to use the
countif
formula to search every row from A1 to A100 excluding the current row?
Your
formula COUNTIF(A2:A100,A1) works great but when I drag it down the
column
it searches from that point on and not any previous cells.

So is there a way to do search every row from A1:A100 except for the
current
row?
Something like =COUNTIF( IF ( Current cell not = A1 then use the array(
$A2:$A100 )), A1)

or something similar? I hope Im making sense...

"JulieD" wrote in message
...
Hi Grant

one option
=IF(ISNUMBER(MATCH(A1,A2:A100,0)),"found","not found")

or
=IF(COUNTIF(A2:A100,A1)=1,"found","not found")

- or if you want to return the row number of where it is found just use
MATCH(A1,A2:A100,0)+1

Hope this helps
Cheers
JuileD


"Grant" wrote in message
...
I have a number of cells, each containing a different string value. Is
there a way for me to determine whether a string from position A1 in my
sheet, exists anywhere else in that same column on my sheet.

ie does text from Cell A1 match any text between Cell A2 and Cell
A100?

Thanks,
Grant





--

Dave Peterson