Find a value in a cell with a comma delimited list

[sshaffer99]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a column called "Current Msg Type" that contains a numeric value. Here is an example<o></o>
<o></o>
<TABLE class=MsoNormalTable style="WIDTH: 123pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=164 border=0 u1:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 33pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-height-source: userset"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BACKGROUND: #666699; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 33pt; mso-border-alt: solid windowtext .5pt" width=71>Current Msg Type<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BACKGROUND: #666699; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 33pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=93>Found (True/False)<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>0<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>1<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="FALSE">FALSE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>2<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>3<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="FALSE">FALSE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>4<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>5<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>6<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>7<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>8<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="FALSE">FALSE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>9<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="TRUE">TRUE<o></o>
</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 11; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" u1:num>10<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:bool="FALSE">FALSE<o></o>
</TD></TR></TBODY></TABLE>
<o></o>
I have another column with a range called "ActiveMsgTypes" where each cell has a comma delimited list. Here is an example.<o></o>
<o></o>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 0.25pt; WIDTH: 1.5in; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=144 border=0 u1:str><COLGROUP><COL style="WIDTH: 169pt; mso-width-source: userset; mso-width-alt: 8228" width=225><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; BACKGROUND: #666699; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt" width=144>Active Message Types<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=144>2, 49, 50, 62, 70, 1<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=144>2, 49, 50, 62, 70<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=144>2, 49, 50, 62, 70, 1, 4<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=144>2, 49, 50, 62, 70<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=144>2, 49, 50, 62, 70<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=144>2, 49, 50, 62, 70<o></o>
</TD></TR></TBODY></TABLE>
<o></o>
<o></o>
I am trying to write a formula to determine if the number under Current Msg Type is found anywhere in the range 'ActiveMsgTypes". The formula is in the column called "Found" and is True/False next to the first column. <o></o>
<o></o>
I have tried the following, but the formula is not finding the value in the range. The number 1 is in both lists, but the formula is coming up with 'False'. The formula I am using is: <o></o>
<o></o>
=IF((ISNUMBER(FIND(A7,ActiveMsgTypes))),TRUE,FALSE)<o></o>
<o></o>
Please note that I have not copied the entire dataset. But the number 1 is in both lists and showing up as 'False'. <o></o>
<o> </o>
Any idea what I am doing wrong?<o></o>
<o></o>
Thanks for your help!<o></o>
<o></o>
<o></o>
<o> </o>

 

[p45cal]

You're going to struggle creating a formula from built in worksheet functions for this, (looking for just 2 will possibly find it in 62) however:

Excel Workbook
	A	B
6	Current Msg Type	Found (True/False)
7	0	FALSE
8	1	TRUE
9	2	TRUE
10	3	FALSE
11	4	TRUE
12	5	FALSE
13	6	FALSE
14	7	FALSE
15	8	FALSE
16	9	FALSE
17	10	FALSE
18
19
20		Active Message Types
21		2, 49, 50, 62, 70, 1
22		2, 49, 50, 62, 70
23		2, 49, 50, 62, 70, 1, 4
24		2, 49, 50, 62, 70
25		2, 49, 50, 62, 70
26		2, 49, 50, 62, 70
Sheet

with B7 copied down, backed up by the user defined function:

Function blah(TheRange, CheckFor) As Boolean
For Each n In Split(Join(Application.Transpose(TheRange.Value), ","), ",")
  If CLng(n) = CheckFor Then
    blah = True
    Exit For
  End If
Next n
End Function

seems to work for numbers.

 

[Ron Coderre]

With your Current Msg Type list in A1:A12
and
your Active Message Types list in E1:E7

This regular formula returns TRUE if the referenced Col_A value is in the Active Message Types list:

B2: =(SUMPRODUCT(--ISNUMBER(SEARCH(", "&A2&",",", "&$E$2:$E$7&",")))>0)

Copy that formula down throughB12

With your sample data, these are the returned values:
<TABLE style="WIDTH: 119pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=159><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #666699; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=35 width=64>Current Msg Type

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #666699; WIDTH: 71pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" id=td_post_2695929 class=xl66 width=95>Found (True/False)</TD></TR>

<TR style="HEIGHT: 15.75pt; mso-yfti-irow: 1" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center u1:bool="TRUE">FALSE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 2" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>TRUE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 3" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>TRUE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 4" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 5" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>TRUE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 6" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>5</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR>

<TR style="HEIGHT: 15.75pt; mso-yfti-irow: 7" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>6</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 8" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>7</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 9" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>8</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 10" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=64 align=right u1:num>9</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 11; mso-yfti-lastrow: yes" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 height=21 width=64 align=right u1:num>10</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=95 align=center>FALSE</TD></TR></TBODY></TABLE>

 

[Rasm]

Try this - just change the LookAt to xlpart - if you want to look for partial words - or use the code with arrays rather trhan the .union - up to you

http://www.excelfox.com/forum/showthread.php?54-Find-All-Cells-That-Contain-A-Certain-Value

 

[Peter_SSs]

Also possibly this.

Excel Workbook
	A	B
1	Current Msg Type	Found (True/False)
2	0	FALSE
3	1	TRUE
4	2	TRUE
5	3	FALSE
6	4	TRUE
7	5	FALSE
8	6	FALSE
9	7	FALSE
10	8	FALSE
11	9	FALSE
12	10	FALSE
Lookup

 

[Peter_SSs]

Try this ..

I haven't studied the detail of the code linked to but it would have a number of potential disadvantages including ..

1. Not being dynamic. That is if a value was changed in the 'Current Msg Type' columns or in the named range 'ActiveMsgTypes', the value in the 'Found (True/False)' would not automatically update.

2. It's a lot of code when a native excel formula will seemingly do the job.

3. The user would require macros to be enabled.

 

[p45cal]

You're going to struggle creating a formula from built in worksheet functions for this

and within minutes two people do it!.

Do make sure that all commas in the list are followed by a space though (exactly one space in the sumproduct formula).
In mine, no leading or trailing commas, and the type list has to be a single column.

 

[sshaffer99]

I tried Ron's idea and it worked. Thank you all.

 

[p45cal]

I know this has been solved, but here's a version of my udf solution which on testing so far, seems to be immune from:

• trailing or leading commas,
• leading or trailing spaces,
• multiple spaces or commas together,
• the shape of the list of message types; can be a single column, a single row (in fact it can be any shape, including a non-contiguous range)
• the types list containg numbers or text

Function blah(TheRange, CheckFor) As Boolean
For Each cll In TheRange.Cells
  xx = Join(Array(xx, cll), ",")
Next cll
For Each n In Split(xx, ",")
  If Trim(n) = Trim(CheckFor) Then
    blah = True
    Exit For
  End If
Next n
End Function

 

[sshaffer99]

Thank you so much. I will also give this a try.