I have a simple check field with the letters A-G

 

Depending on what the user clicks, I would like to simplify the results annotated as a range:

 

ie.

A-D,E,F

A,B,E-G

A,B,D

etc

 

 

Any thoughts, CF's?

 

NOTE: just added an attachment to work with and noticed a sort has to be applied.   

See if you can adapt:

http://www.briandunning.com/cf/734

alright Comment!

that and a Sort function did it.

 

Thanks

Let ( [ 
	txt = SortList ( tbl::selection ; "Asc" ; "Text" ) ;
	num = Substitute ( txt ; [ "A" ; "1" ]; [ "B" ; "2" ]; [ "C" ; "3" ]; [ "D" ; "4" ]; [ "E" ; "5" ]; [ "F" ; "6" ]; [ "G" ; "7" ]   ) ;
	abrv = CollapseList ( num );
	alph = Substitute ( abrv ; [ "1" ; "A" ]; [ "2" ; "B" ]; [ "3" ; "C" ]; [ "4" ; "D" ]; [ "5" ; "E" ]; [ "6" ; "F" ]; [ "7" ; "G" ]   )
];
alph
)

Sorting can be done simply and quickly (!) by =

FilterValues ( "A¶B¶C¶D¶E¶F¶G" ; Selection )

or, to make it future-proof =

FilterValues ( ValueListItems ( Get ( FileName) ; "YourValueList" ) ; Selection )

An afterthought: you're performing 14 substitutions just to convert the letters to numbers and back, so that you can use the custom function. With 15 substitutions, you could substitute:

A,B,C,D,E,F,G ==> A-G

A,B,C,D,E,F ==> A-F
B,C,D,E,F,G ==> B-G

A,B,C,D,E ==> A-E
B,C,D,E,F ==> B-F
C,D,E,F,G ==> C-G

A,B,C,D ==> A-D
B,C,D,E ==> B-E
C,D,E,F ==> C-F
D,E,F,G ==> D-G

A,B,C ==> A-C
B,C,D ==> B-D
C,D,E ==> C-E
D,E,F ==> D-F
E,F,G ==> E-G

and be done with it.

'inverting' the FilterValues function slick and a new one for me, works great... thanks again!