Identifiy the last version when meets a certain criteria and grouped by RefNo

[#1]

I need to be able to create a Field that flags either yes or no based on a particular set of criteria.  Please see mock table below.

RefNoType	RefSeq	Claim Type State	Claim Type State Done Date	Claim Type State Id Changed Flag
539902AD	1	Notified	08/11/2011 10:40:44	No
539902AD	1	Closed	08/11/2011 10:40:44	Yes
539902PI	1	Closed	08/11/2011 10:40:44	No
539902PI	1	Notified	08/11/2011 10:40:44	No
539902PI	4	ReOpened	13/12/2011 10:10:42	No
539902PI	10	ReClosed	18/04/2012 15:30:51	No
539902PI	11	ReOpened	28/06/2012 09:29:28	No
539902PI	12	ReClosed	29/06/2012 08:43:33	Yes
539902TP	1	Notified	08/11/2011 10:40:44	No
539902TP	2	Closed	08/11/2011 11:06:14	No
539902TP	7	ReOpened	05/01/2012 15:16:36	No
539902TP	8	ReClosed	06/01/2012 13:06:34	Yes

I am trying to create the field highlighted with the red writing.

I need to distinguish when the last RefSeq of a particular RefNoType is either Closed or ReClosed when this is true to show 'Yes'

if it is notified or Reopened or if the there is another RefSeQ after it was CLosed or Reclosed then it should show 'No'

I would appreciate any help.

Thanks in advance

Matt