check if value occurs within a range
 

I have the following in column A :

A
1
2
3 4
3 4 5
3
4
5
1 3


How do I find out if the number 1 is found in Column A. Column A
contains either a single entry or multiple entries separated by
"ALT+Enter" (Chr(10)).

I don't care if it is there more than once, only if it is there at
all.

Any suggestions?

TIA

Heather.


--
peacelittleone


------------------------------------------------------------------------
peacelittleone's Profile: http://www.excelforum.com/member.php...o&userid=20937
View this thread: http://www.excelforum.com/showthread...hreadid=383726

Heather,

This any good?

=COUNTIF(A:A,1)+COUNTIF(A:A,"1"&CHAR(10)&"*")+COUN TIF(A:A,"*"&CHAR(10)&"1")

--

HTH

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


"peacelittleone"
<peacelittleone.1rgmeo_1120169190.0972@excelforu m-nospam.com wrote in
message news:peacelittleone.1rgmeo_1120169190.0972@excelfo rum-nospam.com...

I have the following in column A :

A
1
2
3 4
3 4 5
3
4
5
1 3


How do I find out if the number 1 is found in Column A. Column A
contains either a single entry or multiple entries separated by
"ALT+Enter" (Chr(10)).

I don't care if it is there more than once, only if it is there at
all.

Any suggestions?

TIA

Heather.


--
peacelittleone


------------------------------------------------------------------------
peacelittleone's Profile:
http://www.excelforum.com/member.php...o&userid=20937
View this thread: http://www.excelforum.com/showthread...hreadid=383726

Given that E2: 1

=(COUNTIF(A:A,E2)+COUNTIF(A:A,"*"&E2&"*"))0

peacelittleone wrote:
I have the following in column A :

A
1
2
3 4
3 4 5
3
4
5
1 3


How do I find out if the number 1 is found in Column A. Column A
contains either a single entry or multiple entries separated by
"ALT+Enter" (Chr(10)).

I don't care if it is there more than once, only if it is there at
all.

Any suggestions?

TIA

Heather.

This is what ended up working:

=COUNTIF($A$1:$A$7,B1)+COUNTIF($A$1:$A$7,B1&CHAR(1 0)&"*")+COUNTIF($A$1:$A$7,"*"&CHAR(10)&B1)+COUNTIF ($A$1:$A$7,"*"&CHAR(10)&B1&CHAR(10)&"*")

Where I had to conver the values in column B to text:
=text(B1,"0")


Thanks for getting me started!

Heather.


--
peacelittleone


------------------------------------------------------------------------
peacelittleone's Profile: http://www.excelforum.com/member.php...o&userid=20937
View this thread: http://www.excelforum.com/showthread...hreadid=383726

I think you want the formula distinguish between true 1 and 1 that
occurs in such items like 11...

=SUMPRODUCT(ISNUMBER(SEARCH(CHAR(10)&B1&CHAR(10),C HAR(10)&$A$1:$A$7&CHAR(10)))+0)0

would be more compact.

peacelittleone wrote:
This is what ended up working:

=COUNTIF($A$1:$A$7,B1)+COUNTIF($A$1:$A$7,B1&CHAR(1 0)&"*")+COUNTIF($A$1:$A$7,"*"&CHAR(10)&B1)+COUNTIF ($A$1:$A$7,"*"&CHAR(10)&B1&CHAR(10)&"*")

Where I had to conver the values in column B to text:
=text(B1,"0")


Thanks for getting me started!

Heather.