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>

 

[mojo707]

You should adjust the ranges in the formula, changing:
B2:B5 to B2:Bn
D2:D5 to D2:Dn
where n is last row with data.

M.

Yes, I did do that however it stops after 6 rows. I have 28 rows of data now and the adjusted formula is:
=AGGREGATE(15,6,D$2:D$28/((B$2:B$28=B2)*(D$2:D$28>=C2)*ISNA(MATCH(D$2:D$28,E$1:E1,0))),1)
IF I delete the very first row then the first row that shows #NUM ! returns a date value, so it is working but not beyond row 6

 

[Marcelo Branco]

hmm... i'm not seeing your data...

See if this works

E2 copied down
=IFERROR(AGGREGATE(15,6,D$2:D$28/((B$2:B$28=B2)*(D$2:D$28>=C2)*ISNA(MATCH(D$2:D$28,E$1:E1,0))),1),D2)

M.

 

[mojo707]

OK well that removes the error, but it doesn't select the most appropriate date like was done in the first 5 rows, rather it defaults to the value already used in column D. perhaps if you had time and could PM me I could send you the actual spreadsheet?

 

[Marcelo Branco]

I need more details
Try posting a larger sample of your data here - the one with data in rows 2:28 that you mentioned in post 11.

M.

 

[mojo707]

I need more details
Try posting a larger sample of your data here - the one with data in rows 2:28 that you mentioned in post 11.

M.

Here is my latest sample data. I've added a column (G) to show what I would have expected the formula to return based on the available dates in column D

	A	B	C	D	E	F	G
1	Employee ID	Worker Type	Contract Expiry Date	Current Demob Date	Comment	Desired Demob Date (Calculated)	Expected Result
2	1000	Electrician	8/31/2019	<strike>5/31/2020</strike>	date to be used G15	8/31/2019	8/31/2019
3	1001	Electrician	7/31/2019	8/31/2019	date used F2	7/31/2019	7/31/2019
4	1002	Electrician	6/30/2019	7/31/2019	date used F3	9/30/2019	7/31/2019
5	1003	Electrician	5/31/2020	<strike>6/30/2020</strike>	date to be used G21	5/31/2020	5/31/2020
6	1004	Electrician	8/31/2019	5/31/2020	date used F5	1/31/2020	8/31/2019
7	1005	Electrician	1/31/2020	<strike>4/30/2020</strike>	date to be used G14	4/30/2020	1/31/2020
8	1006	Electrician	7/31/2019	<strike>9/30/2019</strike>	date to be used G13	6/30/2020	7/31/2019
9	1007	Electrician	8/31/2019	<strike>5/31/2020</strike>	date to be used G16	#NUM!	8/31/2019
10	1008	Electrician	7/31/2019	<strike>8/31/2019</strike>	date to be used G6	#NUM!	7/31/2019
11	1009	Electrician	6/30/2019	<strike>1/31/2020</strike>	date to be used G7	#NUM!	8/31/2019
12	1010	Electrician	8/31/2019	<strike>5/31/2020</strike>	date to be used G17	#NUM!	8/31/2019
13	1011	Electrician	7/31/2019	<strike>8/31/2019</strike>	date to be used G9	#NUM!	9/30/2019
14	1012	Electrician	5/31/2019	<strike>7/31/2019</strike>	date to be used G4	#NUM!	4/30/2020
15	1013	Electrician	8/31/2019	<strike>5/31/2020</strike>	date to be used G18	#NUM!	5/31/2020
16	1014	Electrician	7/31/2019	<strike>8/31/2019</strike>	date to be used G11	#NUM!	5/31/2020
17	1015	Electrician	6/30/2019	<strike>7/31/2019</strike>	date to be used G8	#NUM!	6/30/2020
18	1016	Electrician	8/31/2019	<strike>5/31/2020</strike>	date to be used G19	#NUM!	5/31/2020
19	1017	Electrician	7/31/2019	<strike>8/31/2019</strike>	date to be used G12	#NUM!	5/31/2020
20	1018	Electrician	6/30/2019	<strike>7/31/2019</strike>	date to be use G10	#NUM!	5/31/2020
21	1019	Electrician	8/31/2019	<strike>5/31/2020</strike>	date to be used G20	#NUM!	5/31/2019

<colgroup><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Maybe this...
I think it's OK, but i found two different results (in yellow)

	A​	B​	C​	D​	E​	F​	G​
1​	Employee ID​	Worker Type​	Contract Expiry Date​	Current Demob Date​	Comment​	Desired Demob Date (Calculated)​	Expected Result​
2​	1000​	Electrician​	08/31/2019​	05/31/2020​	date to be used G15​	08/31/2019​	08/31/2019​
3​	1001​	Electrician​	07/31/2019​	08/31/2019​	date used F2​	07/31/2019​	07/31/2019​
4​	1002​	Electrician​	06/30/2019​	07/31/2019​	date used F3​	07/31/2019​	07/31/2019​
5​	1003​	Electrician​	05/31/2020​	06/30/2020​	date to be used G21​	05/31/2020​	05/31/2020​
6​	1004​	Electrician​	08/31/2019​	05/31/2020​	date used F5​	08/31/2019​	08/31/2019​
7​	1005​	Electrician​	01/31/2020​	04/30/2020​	date to be used G14​	01/31/2020​	01/31/2020​
8​	1006​	Electrician​	07/31/2019​	09/30/2019​	date to be used G13​	07/31/2019​	07/31/2019​
9​	1007​	Electrician​	08/31/2019​	05/31/2020​	date to be used G16​	08/31/2019​	08/31/2019​
10​	1008​	Electrician​	07/31/2019​	08/31/2019​	date to be used G6​	07/31/2019​	07/31/2019​
11​	1009​	Electrician​	06/30/2019​	01/31/2020​	date to be used G7​	08/31/2019​	08/31/2019​
12​	1010​	Electrician​	08/31/2019​	05/31/2020​	date to be used G17​	08/31/2019​	08/31/2019​
13​	1011​	Electrician​	07/31/2019​	08/31/2019​	date to be used G9​	09/30/2019​	09/30/2019​
14​	1012​	Electrician​	05/31/2019​	07/31/2019​	date to be used G4​	04/30/2020​	04/30/2020​
15​	1013​	Electrician​	08/31/2019​	05/31/2020​	date to be used G18​	05/31/2020​	05/31/2020​
16​	1014​	Electrician​	07/31/2019​	08/31/2019​	date to be used G11​	05/31/2020​	05/31/2020​
17​	1015​	Electrician​	06/30/2019​	07/31/2019​	date to be used G8​	05/31/2020​	06/30/2020​
18​	1016​	Electrician​	08/31/2019​	05/31/2020​	date to be used G19​	05/31/2020​	05/31/2020​
19​	1017​	Electrician​	07/31/2019​	08/31/2019​	date to be used G12​	05/31/2020​	05/31/2020​
20​	1018​	Electrician​	06/30/2019​	07/31/2019​	date to be use G10​	05/31/2020​	05/31/2020​
21​	1019​	Electrician​	08/31/2019​	05/31/2020​	date to be used G20​	06/30/2020​	05/31/2019​

<tbody>
</tbody>

Formula in F2 copied down
=AGGREGATE(15,6,D$2:D$21/((B$2:B$21=B2)*(D$2:D$21>=C2)*(COUNTIFS(B$2:B$21,B2,D$2:D$21,D$2:D$21)>COUNTIFS(B$1:B1,B2,F$1:F1,D$2:D$21))),1)

Remark:
It's important to test with multiple (at least two) Worker Type in column B.
Could you provide such data sample along with expected results for testing purposes?

M.

 

[Marcelo Branco]

See if this clarifies and shows that the formula works.
I added column F to show from which row the results of column E come from.

	A​	B​	C​	D​	E​	F​
1​	Employee ID​	Worker Type​	Contract Expiry Date​	Current Demob Date​	Desired Demob Date (Calculated)​	Date Used From​
2​	1000​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D3​
3​	1001​	Electrician​	07/31/2019​	08/31/2019​	07/31/2019​	D4​
4​	1002​	Electrician​	06/30/2019​	07/31/2019​	07/31/2019​	D14​
5​	1003​	Electrician​	05/31/2020​	06/30/2020​	05/31/2020​	D2​
6​	1004​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D10​
7​	1005​	Electrician​	01/31/2020​	04/30/2020​	01/31/2020​	D11​
8​	1006​	Electrician​	07/31/2019​	09/30/2019​	07/31/2019​	D17​
9​	1007​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D13​
10​	1008​	Electrician​	07/31/2019​	08/31/2019​	07/31/2019​	D20​
11​	1009​	Electrician​	06/30/2019​	01/31/2020​	08/31/2019​	D16​
12​	1010​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D19​
13​	1011​	Electrician​	07/31/2019​	08/31/2019​	09/30/2019​	D8​
14​	1012​	Electrician​	05/31/2019​	07/31/2019​	04/30/2020​	D7​
15​	1013​	Electrician​	08/31/2019​	05/31/2020​	05/31/2020​	D6​
16​	1014​	Electrician​	07/31/2019​	08/31/2019​	05/31/2020​	D9​
17​	1015​	Electrician​	06/30/2019​	07/31/2019​	05/31/2020​	D12​
18​	1016​	Electrician​	08/31/2019​	05/31/2020​	05/31/2020​	D15​
19​	1017​	Electrician​	07/31/2019​	08/31/2019​	05/31/2020​	D18​
20​	1018​	Electrician​	06/30/2019​	07/31/2019​	05/31/2020​	D21​
21​	1019​	Electrician​	08/31/2019​	05/31/2020​	06/30/2020​	D5​

Formula in E2 copied down
=AGGREGATE(15,6,D$2:D$21/((B$2:B$21=B2)*(D$2:D$21>=C2)*(COUNTIFS(B$2:B$21,B2,D$2:D$21,D$2:D$21)>COUNTIFS(B$1:B1,B2,E$1:E1,D$2:D$21))),1)

M.

 

[mojo707]

Yes! that seems to work. I will do as you say and test with the different employee type. Thanks Marcelo really appreciate your time in helping.

 

[mojo707]

See if this clarifies and shows that the formula works.
I added column F to show from which row the results of column E come from.

	A​	B​	C​	D​	E​	F​
1​	Employee ID​	Worker Type​	Contract Expiry Date​	Current Demob Date​	Desired Demob Date (Calculated)​	Date Used From​
2​	1000​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D3​
3​	1001​	Electrician​	07/31/2019​	08/31/2019​	07/31/2019​	D4​
4​	1002​	Electrician​	06/30/2019​	07/31/2019​	07/31/2019​	D14​
5​	1003​	Electrician​	05/31/2020​	06/30/2020​	05/31/2020​	D2​
6​	1004​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D10​
7​	1005​	Electrician​	01/31/2020​	04/30/2020​	01/31/2020​	D11​
8​	1006​	Electrician​	07/31/2019​	09/30/2019​	07/31/2019​	D17​
9​	1007​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D13​
10​	1008​	Electrician​	07/31/2019​	08/31/2019​	07/31/2019​	D20​
11​	1009​	Electrician​	06/30/2019​	01/31/2020​	08/31/2019​	D16​
12​	1010​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​	D19​
13​	1011​	Electrician​	07/31/2019​	08/31/2019​	09/30/2019​	D8​
14​	1012​	Electrician​	05/31/2019​	07/31/2019​	04/30/2020​	D7​
15​	1013​	Electrician​	08/31/2019​	05/31/2020​	05/31/2020​	D6​
16​	1014​	Electrician​	07/31/2019​	08/31/2019​	05/31/2020​	D9​
17​	1015​	Electrician​	06/30/2019​	07/31/2019​	05/31/2020​	D12​
18​	1016​	Electrician​	08/31/2019​	05/31/2020​	05/31/2020​	D15​
19​	1017​	Electrician​	07/31/2019​	08/31/2019​	05/31/2020​	D18​
20​	1018​	Electrician​	06/30/2019​	07/31/2019​	05/31/2020​	D21​
21​	1019​	Electrician​	08/31/2019​	05/31/2020​	06/30/2020​	D5​

<tbody>
</tbody>

Formula in E2 copied down
=AGGREGATE(15,6,D$2:D$21/((B$2:B$21=B2)*(D$2:D$21>=C2)*(COUNTIFS(B$2:B$21,B2,D$2:D$21,D$2:D$21)>COUNTIFS(B$1:B1,B2,E$1:E1,D$2:D$21))),1)

M.

The testing is going OK but I am noticing that when there are more than around 100 rows of data (I have 4000) the calculation takes hours in excel to complete (and that's on an i7 with 16GB RAM). Is there are way to make the formula more efficient? I have tried putting my data into an excel table and instead of referencing columns as A:A etc referencing thetable i.e. Table 1[Column 1] but then the formula stops working...

 

[Marcelo Branco]

Avoid references to whole columns like A:A - try something like A2:A4000
If performance is still very poor, maybe a macro might be the solution. I'm busy - maybe someone else can help you with a macro.

M.