I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords that is
forbidden to appear in the name. The list is stored in worksheet 2. How can
I perform this task in Excel?

Many thanks!!

Hi!

So, what is it that you want to do? Identify student names that contain the
"forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords that
is forbidden to appear in the name. The list is stored in worksheet 2. How
can I perform this task in Excel?

Many thanks!!

Hi Biff....

I dunno if your response helped the Op or not, his need was not real clear
to me......but I really really do like your CF offering....Good Job
there!.....one for my stash of nifty things.

Vaya con Dios,
Chuck, CABGx3


"Biff" wrote in message
...
Hi!

So, what is it that you want to do? Identify student names that contain
the
"forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords that
is forbidden to appear in the name. The list is stored in worksheet 2.
How
can I perform this task in Excel?

Many thanks!!

Thanks, Chuck!

You should've seen some of the "forbidden" words I used in my test!

Incidentally, this is how I interpreted the post:

List of names: (Sheet1)

Joe "forbidden word" Smith
Sue Jones "forbidden word"
"forbidden word" Jenkins
Jane Doe

List of "forbidden words": (Sheet2)

word
word
word

Biff

"CLR" wrote in message
...
Hi Biff....

I dunno if your response helped the Op or not, his need was not real clear
to me......but I really really do like your CF offering....Good Job
there!.....one for my stash of nifty things.

Vaya con Dios,
Chuck, CABGx3


"Biff" wrote in message
...
Hi!

So, what is it that you want to do? Identify student names that contain
the
"forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords
that
is forbidden to appear in the name. The list is stored in worksheet 2.
How
can I perform this task in Excel?

Many thanks!!

Lolol Biff.....
My test "forbidden words" were much jucier!

Not criticizing your interpretation of the post.....much better than what I
could visualize.....In fact, as you know, that's usually the hardest part of
responding, is to figure out what the OP really wants. And of course it's
not really their fault either.

And seriously, I really did admire your CF solution, and indeed will save it
in my goodie-box for some future need.

Vaya con Dios,
Chuck, CABGx3


"Biff" wrote in message
...
Thanks, Chuck!

You should've seen some of the "forbidden" words I used in my test!

Incidentally, this is how I interpreted the post:

List of names: (Sheet1)

Joe "forbidden word" Smith
Sue Jones "forbidden word"
"forbidden word" Jenkins
Jane Doe

List of "forbidden words": (Sheet2)

word
word
word

Biff

"CLR" wrote in message
...
Hi Biff....

I dunno if your response helped the Op or not, his need was not real
clear
to me......but I really really do like your CF offering....Good Job
there!.....one for my stash of nifty things.

Vaya con Dios,
Chuck, CABGx3


"Biff" wrote in message
...
Hi!

So, what is it that you want to do? Identify student names that contain
the
"forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of
the
columns represent the student names. But there is a list of keywords
that
is forbidden to appear in the name. The list is stored in worksheet 2.
How
can I perform this task in Excel?

Many thanks!!

really sorry for my poor presentation...
but still very appreciate your help!! thanks!

what i mean is that i have a list of "forbidden words" that cannot appear on
the student names.
for example, the list is {sex, jesus, coca cola, sony, fxxk, ...}
i want to identify the students whose names contain those "forbidden words".
your suggestion has just given me a big help indeed, thanks! but if i also
want to filter the problem (formatted) records, that means to do something
like "auto filter". how can i achieve this?
or can i set any formula to highlight those records by a TRUE/FALSE value?

Many thanks again!!

"Biff" l...
Hi!

So, what is it that you want to do? Identify student names that contain
the "forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords that
is forbidden to appear in the name. The list is stored in worksheet 2. How
can I perform this task in Excel?

Many thanks!!

Hi!

You can use the same formula in a helper column. Just make sure you enter
the formula as an array using the key combo of CTRL,SHIFT,ENTER. The formula
will return either TRUE or FALSE. TRUE meaning the name contains a fobidden
word. Then you can filter on that column.

Biff

"guy" wrote in message
...
really sorry for my poor presentation...
but still very appreciate your help!! thanks!

what i mean is that i have a list of "forbidden words" that cannot appear
on the student names.
for example, the list is {sex, jesus, coca cola, sony, fxxk, ...}
i want to identify the students whose names contain those "forbidden
words".
your suggestion has just given me a big help indeed, thanks! but if i also
want to filter the problem (formatted) records, that means to do something
like "auto filter". how can i achieve this?
or can i set any formula to highlight those records by a TRUE/FALSE value?

Many thanks again!!

"Biff"
l...
Hi!

So, what is it that you want to do? Identify student names that contain
the "forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords that
is forbidden to appear in the name. The list is stored in worksheet 2.
How can I perform this task in Excel?

Many thanks!!

Really appreciate your help and quick response!! Thanks!

"Biff" l...
Hi!

You can use the same formula in a helper column. Just make sure you enter
the formula as an array using the key combo of CTRL,SHIFT,ENTER. The
formula will return either TRUE or FALSE. TRUE meaning the name contains a
fobidden word. Then you can filter on that column.

Biff

"guy" wrote in message
...
really sorry for my poor presentation...
but still very appreciate your help!! thanks!

what i mean is that i have a list of "forbidden words" that cannot appear
on the student names.
for example, the list is {sex, jesus, coca cola, sony, fxxk, ...}
i want to identify the students whose names contain those "forbidden
words".
your suggestion has just given me a big help indeed, thanks! but if i
also want to filter the problem (formatted) records, that means to do
something like "auto filter". how can i achieve this?
or can i set any formula to highlight those records by a TRUE/FALSE
value?

Many thanks again!!

"Biff" l...
Hi!

So, what is it that you want to do? Identify student names that contain
the "forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords
that is forbidden to appear in the name. The list is stored in worksheet
2. How can I perform this task in Excel?

Many thanks!!