FilterValues, part 2

Sometimes it happens that the real value of a blog posting emerges in the comments section. Such was the case last time, when Paul Jansen posted an elegant little workaround to remedy a performance issue with the FilterValues function. Paul’s workaround is deserving of its own demo, and that’s what we’re going to look at today.

Demo file: filtering-values-v2.zip

To briefly recap, the performance issue is this: When there is a significant disparity between the length of the two lists,

FilterValues ( long list ; short list )

…runs considerably faster than…

FilterValues ( short list ; long list )

…and if the two statements produced identical output, you could simply, as a best practice, always use the longer of the two lists as the first argument.

However, as is easily demonstrated, the two statements are not equivalent. When you use FilterValues to filter a pair of lists, whichever list comes first will determine the order of items in the resulting output.

vs.

…and at the risk of stating the obvious, sometimes we care about the order of items in the result, and sometimes we don’t. If we don’t care about the result order then we can simply go with long/short and get on with our day, but assuming we do care, is there any way to get the best of both worlds (fast performance, and short list as the first argument)?

It turns out there is. First let’s try short/long without the workaround…

On my elderly Windows laptop, this particular set of criteria takes nearly 18 seconds to render. Now let’s try the same settings with Paul’s workaround.

Wow… less than one second. How is this accomplished? By the simple expedient of wrapping one instance of FilterValues inside another like so:

Actually, the script in today’s demo breaks the above into two steps with commentary…

…but the result is the same either way. Paul also points out that you can use a custom function (included in the demo file) to automate the process.

I don’t know about you, but I’m impressed.