Multiple lookups

[oysterriver]

This question refers to a sample file available at: http://www.speedyshare.com/files/30595873/License_Lookup_for_Chandoo_Forum.xls.


There are columns showing the name of a driver, the type of license each driver has, and the date each license was received. I need to determine if there was ever a gap for any type of licensure for each driver (assuming each license is valid for 2 years). I'm stuck on the fact that most drivers have multiple licenses, issued on multiple dates. It seems as if I should do some kind of a lookup/match formula, but I'm confused how I can get it to find all the dates corresponding to each of the driver's licenses and calculate the dates between. Any ideas would be gratefully accepted.

 

[Luke M]

Are the dates in order? Can they be? Assuming there are, this might work...

Create a helper column (let's say col D) to combine the driver name & license type. Formula will be something like:

=A2&B2

In a 2nd helper column, (say, col E), formula is:

=IF(COUNTIF($D$1:D2,D2)=1,"",IF(SUMPRODUCT(MAX(($D1:D$2=D2)*($C1:C$2)))<DATE(YEAR(C2)-2,MONTH(C2),DAY(C2)),"FLAG",""))


This should flag any entry where there was a gap of 2 years.

 

[oysterriver]

Thanks very much, Luke. I've been playing with it for a couple of hours, but there are still some glitches due to the dates. I think the problem is that the same driver can have multiple licenses on the same day, so I can't seem to do a clean sort. When I figure out what's happening, I'll post an update.


Thanks again

 

[Elesario]

Assuming you're able to reorder the data then the following might help:


Sort the columns in ascending order Driver 1st, License 2nd and Date 3rd.


In column D put formula:


=IF(AND(B2=B3, A2=A3), IF(DATE(YEAR(C2)+2, MONTH(C2), DAY(C2))<C3, "Gap", ""), "End")


and fill down.


It should show the word 'Gap' where the consecutive license periods have a gap greater than 2 years. You might want to modify the date boundary condition slightly where it's allowable for the license to end one day and recommence the following day.