Back in the April/May 2005 issue, I wrote a tip titled "True Value Filtering." It demonstrated a recursive custom function that compares two return-separated lists and shows the values that were different between the two lists. It's the opposite result of the FilterValues function that returns the values that are the same between two return-separated lists.
For example, say you have two return-separated lists:
List 1:
A
B
C
D
List 2:
B
C
The result of the formula would be:
A
D
The current version of the recursive custom function looks like this:
FilterList(List1; List2; Len)
Case(
Len > 0;
FilterList(List1; List2; Len - 1) &
Case(
not PatternCount ("¶" & List2 & "¶"; "¶" & GetValue(List1; Len) & "¶");
GetValue(List1; Len) & "¶"
)
)
You call this custom function with a calculation such as:
FilterValueList(MyListField1; MyListField2; ValueCount(MyListField1))
Unfortunately, this approach can be slow with large lists. Fortunately, Kieren MacMillan came to the rescue with a formula that doesn't use recursion. Instead he uses the Substitute function for significantly greater speeds on large lists. He even gives the option to output three different ways as you'll see at the beginning of the custom function. Otherwise, the custom function is heavily notated with comments to better describe this complex formula. Here's the custom function:
ListDifference(listA, listB; output)
/*
This is a non-recursive difference-of-lists function.
output = 'A' returns anything in listA which is not in listB;
output = 'B' [or empty] returns anything in listB which is not in listA;
output = 'both' returns anything in either list which is not in both.
*/
Case(
/* Short-circuit for empty parameter(s). */
output = "A" and IsEmpty ( listA ) ; "" ;
output = "B" and IsEmpty ( listB ) ; "" ;
output = "both" and IsEmpty ( listA ) ; listB ;
output = "both" and IsEmpty ( listB ) ; listA ;
/* No short-circuits -- proceed. */
Let([
/* Build the 'before' list, based on the output choice. */
@_listBefore = Case ( output = "both" ; listA & "¶" & listB ; output = "A" ; listA ; listB ) ;
/* Double-tokenize, to avoid 'consecutive duplicate value' bug.*/
@_listBeforeDT = Substitute ( @_listBefore ; ¶ ; "¶¶" ) ;
/* Build the 'thru' list, based on the output choice. */
@_listThru = Case ( output = "both" ; FilterValues ( listA ; listB ) ; output = "A" ; listB ; listA ) ;
/* Build a Substitute formula from the two lists. */
@_header = "Substitute ( " & Quote ( ¶ & @_listBeforeDT & ¶ ) & " ; [ \"\¶" ;
@_footer = "\¶\" ; \"\" ] )" ;
@_formula = @_header & Substitute ( @_listThru ; ¶ ; "\¶\" ; \"\" ] ; [ \"\¶" ) & @_footer ;
/* Fix the double-token subsitution parameter(s), to avoid erroneous hard-return removal. */
@_formulaFixed = Substitute ( @_formula ; " [ \"\¶\¶\" ; \"\" ] " ; " [ \"\¶\¶\" ; \¶ ] " )
];
If(
/* Exit on formula length error. */
Length ( @_formulaFixed ) > 30000 ; "ERROR: Excessive formula length." ;
/* Evaluate the formula. */
Let([
@_resultDT = Evaluate ( @_formulaFixed ) ;
/* Un-double-tokenize. */
@_result = Substitute ( @_resultDT ; "¶¶" ; ¶ )
] ;
/* Eliminate the leading and trailing hard returns, and return the final result. */
Middle ( @_result; 2; Length ( @_result ) - 2 )
))))
To call this formula from a calculation, you might use the following formula:
ListDifference(MyListField1; MyListField2; "A")
When you get this custom function entered properly, try it out on a large return-separated list and watch how fast it runs! In an example I ran using a list of 3,172 values compared to a list of six values, the recursive custom function took nearly 30 seconds and the custom function using Substitute took less than one second.