Selecting a 'Minimum' text value

T

TroyS

i have a series of text values such as S01, S02, S15, S23, M08, M08.03, D23,
D24, D35, etc which are associated/contained in a set of records where the
commonality is project number.

So, Project1 may be associated with S01, S15, S33
Project2 may be associated with M08, M08.03, M08.03.01, M08.03.01
Project3 may only have 1 value S01
these values all suggest a hierachy

Somehow i need to be able select the 'top' or 'minimum' value per project
number in order to aggregate other information together such as FTE counts
and percents completes and milestones.

Any suggestions on how to 'find' the top or 'minimum' value?
I have thought about trying to use Min() or use a substring to strip off
the 1st letter and cast it as a number value and then select the Min()?

any other ideas that would be better than what i've come up with would be
helpful...

thx.
 
J

JackD

You don't give very much information.
What sort of records are you using and what do you mean by associated.

If the set of records is one of the text fields of the project tasks then
I'd do something like:

dim minString as string
minString = "Z"
for each task in activeproject.tasks
foo = left(text.text1,1)
if foo < minString then
minString = foo
end if
next task
 
J

John

TroyS said:
i have a series of text values such as S01, S02, S15, S23, M08, M08.03, D23,
D24, D35, etc which are associated/contained in a set of records where the
commonality is project number.

So, Project1 may be associated with S01, S15, S33
Project2 may be associated with M08, M08.03, M08.03.01, M08.03.01
Project3 may only have 1 value S01
these values all suggest a hierachy

Somehow i need to be able select the 'top' or 'minimum' value per project
number in order to aggregate other information together such as FTE counts
and percents completes and milestones.

Any suggestions on how to 'find' the top or 'minimum' value?
I have thought about trying to use Min() or use a substring to strip off
the 1st letter and cast it as a number value and then select the Min()?

any other ideas that would be better than what i've come up with would be
helpful...

thx.
Click to expand...

TroyS,
Since you have both alpha and numeric parts to the string, what do you
consider to be the "minimum value"? For example is M08 "less" than S15?

In VBA you might want to take at look at the Sort Method and apply it to
a consolidated master (Insert/Projects). Just make sure to de-select the
option for keeping the outline structure in the sort.

If you need more than what a simple sort will provide, post again with
more information.

Hope this helps.
John
Project MVP
 
Top