Expert challenge >> Find which Advanced Filter criteria is applied for a found/selected entry

[bszathmary]

Hi Wizards,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Following situation<o></o>
· I need to find regularly a number of customers from a long list (20-30k entries) based on their names, eg selecting all customers containing “police” in their names. <o></o>
· As the list is multi-lingual, I set up a set of criteria and use Excel’s ADVANCED FILTER function to get them. In the meanwhile I have defined more than 600 criteria. J<o></o>
· Now lately I got quite a few entries selected, which I cannot explain. But it is obviously almost impossible to find the criteria which caused this entry to be selected within the 600.<o></o>
<o> </o>
So I would need a UDF <o></o>

Function SHOW_CRITERIA (ByVal Entry as Range, _<o></o>​

CriteriaRange as Range) as String<o></o>​

<o> </o>
This function would take the Entry, which is the selected cell, loop through the criteria range and provide me with the line indices of the criteria, which are fulfilled for this selection. This obviously works only for simple pattern match criteria and not, where the criteria is spread across several lines (OR).<o></o>
<o> </o>
So for example SHOW_CRITERIA (“New York Police Authority”, A1:A5) would return “1,3” if the cells A1:A5 are filled <o></o>
<o> </o>
<TABLE class=MsoTableGrid style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; MARGIN: auto auto auto 5.4pt; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 3cm; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid black .5pt; mso-border-themecolor: text1" vAlign=top width=113>New*<o></o>
</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 3cm; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=113>Government<o></o>
</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 3cm; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=113>*Police*<o></o>
</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 3cm; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=113>Authorities<o></o>
</TD></TR><TR style="mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: black 1pt solid; WIDTH: 3cm; PADDING-TOP: 0cm; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=113>*Court*<o></o>
</TD></TR></TBODY></TABLE><o> </o>
Next step would be to put in a whole selected line (with several cells) and get the results also for a number of AND criteria (in the same line) or OR criteria (in several lines). How would the results be displayed for the latter?<o></o>
<o> </o>
But there might be also a more elegant version for a solution.<o></o>
<o> </o>
Your help is very much appreciated!<o></o>
<o> </o>
Thanks<o></o>
Balazs<o></o>
<o> </o>
PS. I use Excel 2007 on XP.
PPS. There was already a similar question posted by SilentJohn on Sep 2nd 2009 with a title "Advanced Filter:Finding which criteria set a result is from" but there was no proper answer to that

 

[Andrew Poulsom]

Try:

Function SHOW_CRITERIA(ByVal Entry, CriteriaRange As Range) As String
    Dim r As Long
    Dim x As Variant
    Dim Cell As Range
    r = 1
    For Each Cell In CriteriaRange
        On Error Resume Next
        x = WorksheetFunction.Search(Cell.Value, Entry)
        If Err = 0 Then
            SHOW_CRITERIA = SHOW_CRITERIA & r & ","
        Else
            Err.Clear
        End If
        On Error GoTo 0
        r = r + 1
    Next Cell
    SHOW_CRITERIA = Left(SHOW_CRITERIA, Len(SHOW_CRITERIA) - 1)
End Function

 

[bszathmary]

Andrew,

Thanks, that works fine, although it takes some time to loop through all the criteria.

The next challenge would be to have a function to cover OR criteria (ie in two different cells in different columns). Any ideas? But this is not that urgent, just in case...

Thanks
Balazs