Excel filter on words within words

[nakita_dog]

I am looking to setup a filter within Excel that will show me all the rows within one worksheet that contain full words from another worksheet.

For example:

This is in Worksheet A:

[TABLE="width: 164"]
<tbody>[TR]
[TD]Word List A[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 164"]
<tbody>[TR]
[TD]email software[/TD]
[/TR]
[TR]
[TD]free email client[/TD]
[/TR]
[TR]
[TD]free email programs[/TD]
[/TR]
[TR]
[TD]free email service[/TD]
[/TR]
[TR]
[TD]freeware email software[/TD]
[/TR]
[TR]
[TD]mac email programs[/TD]
[/TR]
[TR]
[TD]macosx email programs[/TD]
[/TR]
[TR]
[TD]mail blast[/TD]
[/TR]
</tbody>[/TABLE]

This is in Worksheet B:
Word List B
free
mac
mail

When I run this filter, I want to find all the words that are in list A that contain words from list B.

That should result in just these words:

[TABLE="width: 134"]
<tbody>[TR]
[TD]free email client[/TD]
[/TR]
[TR]
[TD]free email programs[/TD]
[/TR]
[TR]
[TD]free email service[/TD]
[/TR]
[TR]
[TD]mac email programs[/TD]
[/TR]
[TR]
[TD]mail blast
[/TD]
[/TR]
</tbody>[/TABLE]
How can I set that up?

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Enter the formula in B2, copy down, then AutoFilter for column B equals TRUE:

Excel 2010

	A	B	C	D
Sentences	Filter	Words
email software	free
free email client	mac
free email programs	mail
free email service
freeware email software
mac email programs
macosx email programs
mail blast

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&D$2:D$4&" "," "&A2&" "))))>0[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[nakita_dog]

That is perfect. Thank you very much.