Find a value in a cell with a comma delimited list

sshaffer99 · Apr 25, 2011

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><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 · Apr 25, 2011

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:

Code:

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 · Apr 25, 2011

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:

Code:

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 · Apr 25, 2011

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 · Apr 25, 2011

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 · Apr 25, 2011

Rasm said:
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 · Apr 25, 2011

p45cal said:
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 · Apr 25, 2011

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

p45cal · Apr 26, 2011

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

Code:

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 · Apr 26, 2011

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

Find a value in a cell with a comma delimited list

sshaffer99

Board Regular

Excel Facts

p45cal

Well-known Member

Ron Coderre

MrExcel MVP

Rasm

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

p45cal

Well-known Member

sshaffer99

Board Regular

p45cal

Well-known Member

sshaffer99

Board Regular

Similar threads

Forum statistics

Share this page

Find a value in a cell with a comma delimited list

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock