Checklist

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 Insert>Name>Define
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 Format>Conditional 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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 Insert>Name>Define
> 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 Format>Conditional 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:eH%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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 Insert>Name>Define
>> 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 Format>Conditional 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:eH%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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 Insert>Name>Define
> >> 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 Format>Conditional 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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 {***, 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" <[email protected]> 撰寫於郵件新聞:[email protected]...
> 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 Insert>Name>Define
> 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 Format>Conditional 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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
news:[email protected]...
> 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 {***, 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" <[email protected]>
> 撰寫於郵件新聞:[email protected]...
>> 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 Insert>Name>Define
>> 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 Format>Conditional 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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" <[email protected]> 撰寫於郵件新聞:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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 {***, 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" <[email protected]> 撰寫於郵件新聞:[email protected]...
>>> 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 Insert>Name>Define
>>> 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 Format>Conditional 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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!!
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>