Sorting a value list

[Tpaairman]

Not sure where to ask this one, so here it is.

 

This one is for fun - tracking lottery numbers.  I've got a file that I've entered every lottery draw in the state since it started (the list is downloadable online and I was able to import it to FMP).  Here's the problem.  (And it's not a huge deal but since I can't figure it out, it has become a challenge).  The list shows the balls for each draw in the order they were drawn, not numerical order.  In other words out of 53 balls, let's say tonight they were drawn as follows:  7, 53, 24, 16, 10, 40.  

 

My file has 6 ball fields labeled One, Two, Three, Four, Five and Six - I spelled them out since numbers get messy in calculations.  So the list imported so that One = 07, Two = 53 and so on (using my numbers picked above.)  Keep in mind it does have leading zeros (7 is listed as 07, not 7).  I did come up with a calculation field that is One & Two & Three & Four & Five & Six, so it combines them into one string, but I don't know how to sort the string so the numbers are in order.  It doesn't need to change the values of the individual ball fields.

 

I tried creating a value list with the numbers 01-36, and have it based on the values entered in that combined string, but they are still in the same order as entered.

 

So, how can I create a field that will arrange the numbers in numerical order, instead of order of the fields?  Keep in mind, I don't care if there is a space between the numbers or if they are all run together as one string.

[Raybaudi]

The six fields must be text fields and the numbers must have leading zeros ( max two digit ) and do not exceed 98.

This returns a string like: 071016244053

Let ([
L1 = List ( One ; two ; Three ; Four ; Five ; Six ) ;
v1 = Right ( 0 & Evaluate ( " Min (" &  Substitute ( L1 ; ¶ ; ";" ) & " )" ) ; 2 ) ;
L2 = Substitute ( L1 ; v1 ; 99 ) ;
v2 = Right ( 0 & Evaluate ( " Min (" &  Substitute ( L2 ; ¶ ; ";" ) & " )" ) ; 2 ) ;
L3 = Substitute ( L2 ; v2 ; 99 ) ;
v3 = Right ( 0 & Evaluate ( " Min (" &  Substitute ( L3 ; ¶ ; ";" ) & " )" ) ; 2 ) ;
L4 = Substitute ( L3 ; v3 ; 99 ) ;
v4 = Right ( 0 & Evaluate ( " Min (" &  Substitute ( L4 ; ¶ ; ";" ) & " )" ) ; 2 ) ;
L5 = Substitute ( L4 ; v4 ; 99 ) ;
v5 = Right ( 0 & Evaluate ( " Min (" &  Substitute ( L5 ; ¶ ; ";" ) & " )" ) ; 2 ) ;
L6 = Substitute ( L5 ; v5 ; 99 ) ;
v6 = Right ( 0 & Evaluate ( " Min (" &  Substitute ( L6 ; ¶ ; ";" ) & " )" ) ; 2 )
];
If ( ValueCount ( L1 ) = 6 ; v1 &  v2  &  v3 &  v4  &  v5  &  v6 )
)

[comment]

I tried creating a value list with the numbers 01-36

 

Why 01-36, when there are (you say) 53 balls?

 

 

Anyway, try it along these lines:

FilterValues ( "01¶02¶03¶04¶ ... ¶51¶52¶53" , List ( One ; Two ; Three ; Four ; Five ; Six ) )

This will return a sorted, return-separated list of the 6 numbers, which you can arrange any way you like.

 

---

 

If you do have a value list of the 53 ball "numbers" (with leading zeros), you can use that instead of enumerating them again:

FilterValues ( ValueListItems ( Get( FileName ) ; "YourValueList" ) , List ( One ; Two ; Three ; Four ; Five ; Six ) )

Edited February 24, 2015 by comment

[Raybaudi]

Beautiful mind

[Tpaairman]

Thanks for the replies.  I've been away from this for a bit, so I will give them a try.

 

@Comment - about the 36 balls, you are correct.  Should be 53.  The 36 figure stuck in my head from the pick 5 game.