Database Reference
In-Depth Information
POWER USERS' CLINIC: THE POINT OF NO RETURN
RemoveDuplicates()
works great for finding unique values in a list, so long as the last item in the
list has a ¶ (paragraph return) following it. If it doesn't, then the function gets confused and will not
remove the last line, even if it's a duplicate. You can adjust for lists that don't have a trailing ¶ by
adjusting your calculation field slightly:
RemoveDuplicates ( values & "¶," )
You could also modify the custom function itself to guard against this possibility. Doing so makes
the formula more complex, but here it is in case you want a challenge:
Let ( [
cleanList = If ( Right ( values ; 1 ) =
"¶" ; values ; values & "¶" ) ;
first = LeftValues ( cleanList ; 1 )
] ;
first &
If (
ValueCount ( cleanList ) > 1 ;
RemoveDuplicates ( Substitute (
cleanList ; first ; "" ) )
)
)
Figure 16-6
shows an example of a recursive calculation calling the
RemoveDuplicates()
custom function to remove all duplicate colors it finds in the list. (It takes four iterations to
remove all the duplicates and return a unique instance of each item in the list, in the order in
which they appear.)
