Hey, buddy, we can't leave you hanging like that!
One small problem is that your Exp End Year column is text instead of date format. You can't determine the max date from this format.
Here is a bit of a quick & dirty solution. Here is what I did:Did a global change to your date field to make it an actual date, i.e., change 08/09 to 8/1/2009 (American style). Reformatted using Custom mm/yy.
Created a new column at far left to be a "key" that is unique for each row. This key is the concatenation of the course code and the date. The key will show the date as an integer. (This is also called a "helper column" because it gives an intermediate result used later.)
Made this whole block of data a named range.
Created a pivot table. Row labels are course codes. The only column is the date. Changed field options to show Max date. (Shown as an integer.) Now the pivot table shows each course code once, with the latest date.
Add a column after the pivot table to create a key from the pivot table (course code plus date, same as above).
Use VLOOKUP to look up the key generated by the pivot table to match the key in the named range. Repeat for all columns and rows.
How often does the data change? If you update your course data you will have to make sure that the Key column is extended for all rows. Your pivot table will be to be refreshed, and the Key column for the pivot table and all the lookup columns also extended for all rows in the pivot table.
I could not think of a way to do this without helper columns and the pivot table, though one may exist. I could have solved it using VBA but prefer this solution.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks