While we’re on the topic of value list functions, does anyone else love the FilterValues list as much as me? Of course not, that was a rhetorical question. At first I found FilterValues a difficult and confusing function to use, that was before I learned how to stop worrying and love the function. Now it is a common tool in my data analysis toolbox. Let’s explore how it works and what it does, and maybe you will find it just as useful.
FilterValues ( textToFilter ; filterValues )
The first thing to understand is that FilterValues does not filter values out. Usually, we filter something to filter things out of it. In this case, FilterValues is meant to FilterValues in to the final list. The final result will have the items to be filtered, and only those items, left in the list. FilterValues is the bouncer at the door, and they’re only going to allow the values on the approved list through.
The next thing is that it only returns the values from textToFilter that are in the approved list from filterValues. That means, the function will only return values that are in both lists. This is helpful when you want to know if one are more values from filterValues are in another list, textToFilter. It also means that the result is only the values from textToFilter that are in the approved list of filterValues.
The last thing is, the result is in the order of textToFilter, including any duplicates. That means, changing the order of textToFilter and filterValues can have drastically different results. If you want to know if one value is in the list, set the value to textToFilter and the list to filterValues. If you want to know how many times a value shows up in a list, set the value to filterValues and the list to textToFilter. Consider the examples below:
FilterValues ( "Plaid¶Canvas¶Canvas" ; "Canvas¶Plaid" ) = “Plaid¶Canvas¶Canvas”.
Notice that the result is only the values from filterText, but in the order of textToFilter, including duplicates. Also, the function returns an ending carriage return, as detailed in this post.
FilterValues ( "Canvas¶Plaid" ; "Plaid¶Canvas¶Canvas" ) = “Canvas¶Plaid¶”.
In this case, the duplicates are filtered out of the result because the values only show up once in textToFilter. In this way, the function acts like UniqueValues, only it returns only the unique values stated in the textToFilter value list. This is very useful if you have a random list being passed to a function or script that can only accept certain values, or you only want to recognize certain values.
This function also acts like SortValues, only you can set the sort order to be anything you like, instead of just text, number, date, etc. This is very useful if you need the list sorted in a specific order based on some business rules and not simply alphabetically.
A frequent use case is to determine a boolean for if a value exists in a value list. Often the function PatternCount is used, however this function will return a count if the text is a value, or in the middle of a value. It cannot tell the difference between “red” and “tired.” FilterValues will also perform better on long value lists, because it can stop after processing all the values in textToFilter, while PatterCount must evaluate the entire list. Combined with ValueCount, FilterValues gives a simple boolean result if the value is in the list.
ValueCount ( FilterValues ( “Find Value”; $listOfValues ) )
This calculation will return “1” if the value “Find Value” exists in $listOfValues, even if there are multiple duplicates in $listOfValues, and it will return “0” if it is not in $lisOfValues.
Hopefully this has given you some ideas on the power and adaptability of the FilterValues function. With the right knowledge and tools, you can be prepared for anything.
