Help Please

[#1]

Hi All,

I Have an example of a Table like below:

Person	Parcel	Status
1	1	5
1	2	10
1	3	12
2	4	2
2	5	4
2	6	10
2	7	10
2	8	15
3	9	1
3	10	5
3	11	6
3	12	10
3	13	15

I Need to see if the last Parcel (Per Person) has a Status of  '15'. Here I use Max(Parcel), but I need to get all the Parcels Prior to it that has a Status of '10'...

My Answer should look like this and should be in a Table (Straight or Pivot):

Person	Parcel	Status
2	6	10
2	7	10
3	12	10

Any Advice Please?

[#2]

Anyone know how to do this?

[jonathandienst]

Hi

Use

     =FirstSortedValue(Person, -Status)

     =FirstSortedValue(Parcel, -Status)

Regards

Jonathan

[tresesco]

Actually, the logic of calculation is not very clear to me. Why should not there be one following row?

Person	Parcel	Status
1	2	10

[#5]

Tresesco,

Because I need all the parcels Prior to the Max(Parcels) with a Status 15 that has a Status of 10.

And Jonathan, This does not seem to work .

See attached example

[tresesco]

I guess you have to use set analysis with p().

[#7]

Hi , Please find the attached file. I added the FLAG in the script level as 1 for person who reached the Max(Status) else 0.