Filter list based on another list with wildcards

[motosantiago]

Any idea how to accommodate wildcard (*) values in a COUNTIF formula?

For example, I have one large list of company descriptions:
-Fire Damage Restoration
-Fire Damage
-Fire Damage Repiar (spelling mistake purposeful)
-Fire Damage Repair
-Fire Repair Services
-Fire Prevention
-Fire Prevention Services

I need to filter the above list and highlight/remove any phrases that are not also on another list. The tricky part is that the other list contains wildcard phrases, such as:
-Fire Damage*
-Fire Repair*

For example, these should be removed:
-Fire Prevention
-Fire Prevention Services

And these should remain:
-Fire Damage Restoration
-Fire Damage
-Fire Damage Repiar (spelling mistake purposeful)
-Fire Damage Repair
-Fire Repair Services

I tried =MAX(ISNUMBER(SEARCH(whitelist!$A$2:$A$3,A2))+0)

This works ok, but it is ridiculously slow and kills the whole workbook.

Thank you!

 

[kpark91]

Do you allow VBA solutions?

Something along the lines of

    If Cells(i, 1).Value Like "Fire Damage*" Then
    If Cells(i, 1).Value Like "Fire Repair*" Then

 

[markmzz]

Motosantiago,

This Advanced Filter is ok for you?

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Column</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Column</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Fire Damage*</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fire Damage Restoration</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Fire Repair*</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fire Damage</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Fire Damage Repiar (spelling mistake purposeful)</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="FONT-WEIGHT: bold">Column</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fire Damage Repair</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Fire Damage Restoration</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fire Repair Services</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Fire Damage</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Fire Damage Repiar (spelling mistake purposeful)</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Fire Damage Repair</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Fire Repair Services</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Fire Prevention</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Fire Prevention Services</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>



Markmzz

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm a little confused. Your thread title and part of your description refers to filtering but your question asks about using a wildcard in a COUNTIF. So, are you trying to filter or are you trying to count (or maybe both)?