Trying to add a label column based on a variable date parameter

[DonnaEm]

Newish to posting - apologies if I haven't done this right.
I have a sheet with over 300,000 student records. Each student goes through a number of application statuses before being fully enrolled into a cohort of a qualification. I want to be able to look back and see what the status situation would have been at (variable) dates in the past. I'd like to have a new column with 3 labels - "N/A" if the record did not exist because it occurred after the date I've specified, "TRUE" if the record did exist at that date and it was the last record for the student in that student/qualification/cohort and "FALSE" if the record did exist at that time and it was not the last record for the student in that student/qualification/cohort. I can get the "N/A" using =IF(E2<$H$1,"","N/A") but I can't work out how to identify the latest record in each student/qualification/cohort group. Is this possible using a formula or would I need to use VBA? Any ideas would be gratefully received.
A sample of my data:

[TABLE="width: 887"]
<tbody>[TR]
[TD]StudentID
[/TD]
[TD]Status
[/TD]
[TD]Qualification
[/TD]
[TD]Cohort
[/TD]
[TD]changedate
[/TD]
[TD]New column with what I'd like to see:
[/TD]
[TD]As at this (variable) date-->
[/TD]
[TD]20/05/2013
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Under Consideration
[/TD]
[TD]Qualification 1
[/TD]
[TD]062
[/TD]
[TD]13/06/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 2
[/TD]
[TD]071
[/TD]
[TD]6/06/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 2
[/TD]
[TD]071
[/TD]
[TD]19/06/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 2
[/TD]
[TD]071
[/TD]
[TD]25/06/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Under Consideration
[/TD]
[TD]Qualification 2
[/TD]
[TD]071
[/TD]
[TD]6/06/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Faculty
[/TD]
[TD]Qualification 2
[/TD]
[TD]101
[/TD]
[TD]28/05/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 2
[/TD]
[TD]101
[/TD]
[TD]5/06/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Partially Loaded
[/TD]
[TD]Qualification 2
[/TD]
[TD]101
[/TD]
[TD]16/05/2013
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Faculty
[/TD]
[TD]Qualification 2
[/TD]
[TD]011
[/TD]
[TD]11/10/2012
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Faculty
[/TD]
[TD]Qualification 2
[/TD]
[TD]011
[/TD]
[TD]11/10/2012
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 2
[/TD]
[TD]011
[/TD]
[TD]9/11/2012
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 2
[/TD]
[TD]011
[/TD]
[TD]14/12/2012
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Under Consideration
[/TD]
[TD]Qualification 2
[/TD]
[TD]011
[/TD]
[TD]8/10/2012
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Under Consideration
[/TD]
[TD]Qualification 3
[/TD]
[TD]071
[/TD]
[TD]15/07/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Offered Conditional Place
[/TD]
[TD]Qualification 4
[/TD]
[TD]FY2
[/TD]
[TD]22/08/2012
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 4
[/TD]
[TD]FY2
[/TD]
[TD]24/08/2012
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 4
[/TD]
[TD]FY2
[/TD]
[TD]14/12/2012
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Offered Place
[/TD]
[TD]Qualification 5
[/TD]
[TD]071
[/TD]
[TD]29/07/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Partially Loaded
[/TD]
[TD]Qualification 6
[/TD]
[TD]111
[/TD]
[TD]10/10/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Under Consideration
[/TD]
[TD]Qualification 6
[/TD]
[TD]111
[/TD]
[TD]10/10/2013
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi DonnaEm,

If your dataset will always be sorted by StudentID then ChangeDate (oldest to newest) as it is in your example, then this formula should work.

In G2, then copy down:
=IF(E2>$H$1,NA(),OR(A2<>A3,E3>$H$1))

If there's some reason the data might not be sorted and can't be sorted as a first step then I'd suggest using a VBA approach due to the size of your dataset.

 

[DonnaEm]

Hi Jerry
Thank you for your reply - your suggestion is excellent and has helped me solve my problem. I can't sort the whole 300,000 records so I've split them up into smaller chunks that I can sort on. Then I realised that each student can be in more than one qualification and/or more than one cohort within the qualification so I added a helper column with a concatenation of StudentID&Qualification&Cohort. I changed your formula to look at the helper column rather than the StudentID and it works like a charm. Thank you SO much!!