formula to match a date in one column with a date in another

[mojo707]

I'm trying to find a way (I've looked at Index, match, choose, find, search, substitute and am investigating solver) to match from a list of possible project end dates (demobilization dates) the date closest to each employees contract expiry date. The difficulty is that there will be a finite number of demobilization dates and once one is committed (i.e. matched to an employees contract expiry date, it will be unavailable for further matching). The sample worksheet below shows the desired output from the formula that I am seeking for Column E. In the sample you will see that for the Welder, the Desired Demob Date remains unchanged since a Welder cannot be swapped out for an Electrician whereas the Desired Demob Date for Electricians can change and an Electrician can be moved from one team to another.

A	B	C	D	E
Employee ID <tbody> </tbody>	Worker Type <tbody> </tbody>	Contract Expiry Date <tbody> </tbody>	Current Demob Date <tbody> </tbody>	Desired Demob Date <tbody> </tbody>
1	Electrician	08/31/19 <tbody> </tbody>	05/31/20 <tbody> </tbody>	08/31/19 <tbody> </tbody>
2	Electrician	07/31/19 <tbody> </tbody>	08/31/19 <tbody> </tbody>	07/31/19 <tbody> </tbody>
3	Electrician	06/30/19 <tbody> </tbody>	07/31/19 <tbody> </tbody>	05/31/20 <tbody> </tbody>
4	Welder	05/31/20 <tbody> </tbody>	06/30/20 <tbody> </tbody>	06/30/20 <tbody> </tbody>

<tbody>
</tbody>

 

[Marcelo Branco]

I created a macro, but could only test it with just one type of worker (electrician) and the small sample above.
Could you put a larger sample file for testing purposes on a file sharing site? And put in the forum a link to the file.

M.

 

[mojo707]

I created a macro, but could only test it with just one type of worker (electrician) and the small sample above.
Could you put a larger sample file for testing purposes on a file sharing site? And put in the forum a link to the file.

M.

Yes, I'll put it on my drop box site and post the link tomorrow.