I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

Tiziano wrote:
I would like to be able to search and flag a list of
string data based on another list of substring data.

Look at the FIND() or SEARCH() functions. The Help files will tell you
all about them.

Bill

Hi Tiziano

Try this formulae in B column and i think your problem can be solved.

=IF(ISERROR(FIND(C1,A1)),"","X")

And then you can copy this formulae to any place in the B column where u
want this operation to take place.

Regards
"Tiziano" wrote:

I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X"} )

which you need to confirm with control+shift+enter (instead of just with
enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-sensitive.

Tiziano wrote:
I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

Your solution works great, Aladin, except for a tiny
detail...

Say I am going to have a variable number of substrings
in Column C and thus I do not want to include a range
in terms of rows in the function, why does the following
not work?
=LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"})

Am I stuck with something like this?
=LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"", "X"})



"Aladin Akyurek" wrote in message
...
B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X"} )

which you need to confirm with control+shift+enter (instead of just with
enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-sensitive.

Tiziano wrote:
I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

Formulas that operate on arrays like the one I suggested do not admit
whole columns as range references. However, we can do the following:

E2:

=MATCH(REPT("z",255),C:C)

which calculates the position of the last in use in column C.

The formula needs to be modified:

=LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{0, 1},{"","X"})

Still to be confirmed with control+shift+enter.

Tiziano wrote:
Your solution works great, Aladin, except for a tiny
detail...

Say I am going to have a variable number of substrings
in Column C and thus I do not want to include a range
in terms of rows in the function, why does the following
not work?
=LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"})

Am I stuck with something like this?
=LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"", "X"})



"Aladin Akyurek" wrote in message
...

B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X "})

which you need to confirm with control+shift+enter (instead of just with
enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-sensitive.

Tiziano wrote:

I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

Thank you again, Aladin!

While testing your formula, I came across a problem
if one or more of the cells in column C are left
blank while others are populated. (Say, for instance,
I have data in cells C1, C2, C4, but not in C3.
If this happens, then the formula puts an "X" in
column B for every string mentioned in column A.)

I've been thinking about a solution for this, but I
just am not experienced enough to come up with
something. Would you have a suggestion to this
problem?

Thanks.

"Aladin Akyurek" wrote in message
...
Formulas that operate on arrays like the one I suggested do not admit
whole columns as range references. However, we can do the following:

E2:

=MATCH(REPT("z",255),C:C)

which calculates the position of the last in use in column C.

The formula needs to be modified:

=LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{0, 1},{"","X"})

Still to be confirmed with control+shift+enter.

Tiziano wrote:
Your solution works great, Aladin, except for a tiny
detail...

Say I am going to have a variable number of substrings
in Column C and thus I do not want to include a range
in terms of rows in the function, why does the following
not work?
=LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"})

Am I stuck with something like this?
=LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"", "X"})



"Aladin Akyurek" wrote in message
...

B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X "})

which you need to confirm with control+shift+enter (instead of just with
enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-sensitive.

Tiziano wrote:

I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

The right thing to do is not to have any empty cells in the range that
must house the substrings to search for.

Tiziano wrote:
Thank you again, Aladin!

While testing your formula, I came across a problem
if one or more of the cells in column C are left
blank while others are populated. (Say, for instance,
I have data in cells C1, C2, C4, but not in C3.
If this happens, then the formula puts an "X" in
column B for every string mentioned in column A.)

I've been thinking about a solution for this, but I
just am not experienced enough to come up with
something. Would you have a suggestion to this
problem?

Thanks.

"Aladin Akyurek" wrote in message
...

Formulas that operate on arrays like the one I suggested do not admit
whole columns as range references. However, we can do the following:

E2:

=MATCH(REPT("z",255),C:C)

which calculates the position of the last in use in column C.

The formula needs to be modified:

=LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{ 0,1},{"","X"})

Still to be confirmed with control+shift+enter.

Tiziano wrote:

Your solution works great, Aladin, except for a tiny
detail...

Say I am going to have a variable number of substrings
in Column C and thus I do not want to include a range
in terms of rows in the function, why does the following
not work?
=LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"})

Am I stuck with something like this?
=LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{ "","X"})



"Aladin Akyurek" wrote in message
...


B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"", "X"})

which you need to confirm with control+shift+enter (instead of just with
enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-sensitive.

Tiziano wrote:


I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

I was thinking about linking the spreadsheet
that I am building with another existing one.
The substrings in column C would come from
this other spreadsheet. The only way that I
know how to import data is by using a formula
like this:
=[other_spreadsheet.xls]Sheet1!$A$1
=[other_spreadsheet.xls]Sheet1!$A$2
=[other_spreadsheet.xls]Sheet1!$A$3
etc.

The downside of this is that, if there are blank
cells in col. A of other_spreadsheet.xls, they
will also be imported...

I will start another thread with more information
about my wanting to import data from another
spreadsheet as there are a few more details to
mention and this is deviating from my original
question.

Thanks for your help!!





"Aladin Akyurek" wrote in message
...
The right thing to do is not to have any empty cells in the range that
must house the substrings to search for.

Tiziano wrote:
Thank you again, Aladin!

While testing your formula, I came across a problem
if one or more of the cells in column C are left
blank while others are populated. (Say, for instance,
I have data in cells C1, C2, C4, but not in C3.
If this happens, then the formula puts an "X" in
column B for every string mentioned in column A.)

I've been thinking about a solution for this, but I
just am not experienced enough to come up with
something. Would you have a suggestion to this
problem?

Thanks.

"Aladin Akyurek" wrote in message
...

Formulas that operate on arrays like the one I suggested do not admit
whole columns as range references. However, we can do the following:

E2:

=MATCH(REPT("z",255),C:C)

which calculates the position of the last in use in column C.

The formula needs to be modified:

=LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)), {0,1},{"","X"})

Still to be confirmed with control+shift+enter.

Tiziano wrote:

Your solution works great, Aladin, except for a tiny
detail...

Say I am going to have a variable number of substrings
in Column C and thus I do not want to include a range
in terms of rows in the function, why does the following
not work?
=LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"} )

Am I stuck with something like this?
=LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1}, {"","X"})



"Aladin Akyurek" wrote in message
...


B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"" ,"X"})

which you need to confirm with control+shift+enter (instead of just
with enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-
sensitive.

Tiziano wrote:


I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X