Value Number problem....

[Matt Klein]

Anyone have an efficient way to return the value number of a value in a set of values? In other words, I have a set of values:

sp56

sp30

sp25

sp100

and I want to know what value number "sp25" is in that set of values.

I can think of a handful of ways to do this, but none of them are particularly efficient or elegant or simple. I feel like I am missing something so I thought I would pose the question to you fine developers.

Anyone?

[Vaughan]

If you can accept a hard-coded limit you could quickly hack out a function along the lines of

Case(

"sp25" = GetValue( listOfValues ; 1 ) ; 1 ;

"sp25" = GetValue( listOfValues ; 2 ) ; 2 ;

"sp25" = GetValue( listOfValues ; 3 ) ; 3 ;

"sp25" = GetValue( listOfValues ; 4 ) ; 4 ;

"sp25" = GetValue( listOfValues ; 5 ) ; 5 ;

etc

)

Somebody much smarter than me will probably create a custom finction that will be shorter and extend the limit to a huge number of values.

*thinks a while*

Hmmm, maybe something like this might work too... truncate the list of values to the value of interest (specifically the first instance of the value) then count the number of values in the list and add one.

ValueCount ( Left( listofvalues ; Position( listofvalues ; value ; 1 ; 1 ) ) ) = 1

You might need to add or subtract 1 from the Position function to get it to truncate the string correctly, but this might work. It has no hard-coded limits and does not require being a custom function to work. Note that it is completely untested and merely a mind experiment at the moment! :)

[Matt Klein]

Thanks Vaughn!

I ended up going with something very similar to your second example though I counted the number of return characters. I am going to employ the ValueCount method though. I like it better even though it will return the same results.

[Vaughan]

Yes, counting returns is fine as long as the string consistently has or has not got a leading return.

The ValueCount function avoids all that uncertainty.

[comment]

Yes, counting returns is fine as long as the string consistently has or has not got a leading return.

The ValueCount function avoids all that uncertainty.

Yes, but unfortunately the Position function does not. In order to avoid false positives (such as "sp25" being found in "sp250"), use:

Position ( ¶ & list & ¶ ; ¶ & item & ¶ ; 1 ; 1 )