Jump to content

Sorting a value list


Tpaairman
 Share

This topic is 2832 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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 )
)
Link to comment
Share on other sites

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 by comment
  • Like 2
Link to comment
Share on other sites

  • 2 weeks later...

This topic is 2832 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.