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:

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

<tbody>
</tbody>

 

[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!!