Hi there.

I'm trying to modify a custom function called CollapseList by Michael Horak that I found on the Brian Durning website.

Sample input 1:

Gala 2005

Gala 2006

Sample input 2:

Gala 2005

Gala 2006

Gala 2007

Sample input 3:

Gala 2005

Gala 2007

Gala 2008

Sample output 1:

Gala 2005, Gala 2006

Sample output 2:

Gala 2005 to Gala 2007

Sample output 3:

Gala 2005, Gala 2007, Gala 2008

The modification I need is for output 2 to be:

Gala 2005-2007

I understand how to change the placeholder from " to " to a hyphen, I just can't figure out how to change the programming so that only the number portion shows in a range.

I would think this is a simple adjustment, but all my tests have failed. Any masters of recursion out there who could help? I've included Michael Horak's original custom function below.

Thanks!

/*

CollapseList function



Author Michael Horak

http://comment.cjb.net

*COMMENT Visual Realisation





Format

CollapseList ( listOfValues )



Parameters

listOfValues - any text expression or text field



Data type returned

text



Description

Collapses a list of values by replacing serially incrementing sections with their range boundaries. 



The result is a comma-separated list of ranges and individual values. The list separator and the range placeholder can be easily modified or defined as additional function parameters.



Values are compared in the order they appear in the list. For best results, the list should be sorted in ascending order.



July 15, 2007

*/



Let ( [

countValues = ValueCount ( listOfValues ) 

] ;



Case ( 

countValues < 2 ; listOfValues ;



Let ( [ 

// --- MODIFY THESE VALUES TO FORMAT THE RESULT AS DESIRED ---

placeholder = " to " ;

separator = ", " ;



a = GetValue ( listOfValues ; 1 ) ; 

b = GetValue ( listOfValues ; 2 ) ; 

c = GetValue ( listOfValues ; 3 ) ; 



ab = b = SerialIncrement ( a ; 1 ) ;

bc = c = SerialIncrement ( b ; 1 ) ;



writeOut = Case (

a ≠ placeholder ; a ; 

not bc ; placeholder ) ;



nextA = Case ( 

bc and ( ab or a = placeholder ) ; placeholder ; 

b ) ;



sep = Case ( a ≠ placeholder and nextA = b ; separator ) ;



nextList = 

Case ( not IsEmpty ( nextA ) ; nextA & ¶ ) 

& 

RightValues ( listOfValues ; countValues - 2 )



] ; 

writeOut & sep & CollapseList ( nextList )

)



)

)

This is not as simple as it seems: technically, "Gala 2005-7" is just as correct as "Gala 2005-2007" - and so are "Gala 2005-07" and "Gala 2005-la 2007". There needs to be some criteria to distinguish the prefix.

If you do know the prefix (or can calculate it), you could strip it out from the list of values before sending it to the custom function, e.g.:

Let ( [

prefix = LeftWords ( listOfValues ; 1 ) & " " ;

stripped = Substitute ( "¶¶" & listOfValues ; [ ¶ & prefix ; ¶ ] ; [ "¶¶" ; "" ] )

] ;

prefix & CollapseList ( stripped )

)