Keywords match in excel

[Daiwik]

Hi,

I have a list of keywords in Sheet A (2000 rows) and I have a sheet B (column B), where I want to highlight Column B or get YES/NO (in nearby column D) if the cell of column B contains any keyword.

For example - Column B cell contains "30secondMBA.com" and I have a keyword "MBA".
If I use ISNUMBER function, it will be insanely huge which doesn't seem worthy. Please please help me, I have to prepare this sheet today

 

[63falcondude]

Try using a Conditional Formatting formula along the lines of this:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))

where A2:A5 are your keywords and your strings are in column C (starting in row 2).

Or if you want a formula to return YES/NO, you can use the formula (regular formula in the worksheet, not CF formula):

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2)))),"YES","NO)

 

[steve the fish]

Note that this will return potentially incorrect results if you have any blank cells in the A range. Perhaps subtract the count of blanks in the range.

 

[Daiwik]

Try using a Conditional Formatting formula along the lines of this:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))

where A2:A5 are your keywords and your strings are in column C (starting in row 2).

Or if you want a formula to return YES/NO, you can use the formula (regular formula in the worksheet, not CF formula):

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2)))),"YES","NO)

Thanks very much 63falcondude,
Formula is working fine in returning YES/NO but cond.Format. is not working properly. Actually it's highlighting every cell which is not blank. My priority is to highlight keyword matching cells. Please help, my boss will be very happy

 

[Daiwik]

Note that this will return potentially incorrect results if you have any blank cells in the A range. Perhaps subtract the count of blanks in the range.

Yes Steve, Please let me know how should i subtract blank cells. Thanks

 

[63falcondude]

Thanks very much 63falcondude,
Formula is working fine in returning YES/NO but cond.Format. is not working properly. Actually it's highlighting every cell which is not blank. My priority is to highlight keyword matching cells. Please help, my boss will be very happy

If the regular formula worked fine then the CF formula will work fine as well as long as it is applied correctly. See example below.

Highlight C2:C4 > Conditional Formatting > New Rule > Use a Formula > =SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))
Format however you like > OK

The result will look as follows

Excel 2010
	A	B	C
1	Keywords		String
2	apple		30secondMBA.com
3	mouse		The quick brown fox
4	cat		Caterpillars are fun
5	MBA
Sheet1

Make sure that there are not any blank cells in the column A range (A2:A5 in this example).

 

[Daiwik]

If the regular formula worked fine then the CF formula will work fine as well as long as it is applied correctly. See example below.

Highlight C2:C4 > Conditional Formatting > New Rule > Use a Formula > =SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))
Format however you like > OK

The result will look as follows

Excel 2010

	A	B	C
Keywords	String
apple
mouse	The quick brown fox
cat
MBA

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]30secondMBA.com[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Caterpillars are fun[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Make sure that there are not any blank cells in the column A range (A2:A5 in this example).

Thanks 63falcondude, You made my day. It's working great now. Now i'll work on reducing blank cells in the Keyword list because i just got informed that there could be some addition or removing of data. Thanks again for your cooperation

 

[63falcondude]

Thanks 63falcondude, You made my day. It's working great now. Now i'll work on reducing blank cells in the Keyword list because i just got informed that there could be some addition or removing of data. Thanks again for your cooperation

If that's the case, I would make the data from column A into a table and then use the table's column name in place of $A$2:$A$5.