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

M

mojo707

New Member
Joined
Jun 7, 2015
Messages
21
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.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Employee ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]Worker
Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl67, width: 130"]Contract
Expiry Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl67, width: 127"]Current
Demob Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl67, width: 129"]Desired
Demob Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Electrician[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl66, width: 130"]08/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]05/31/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]08/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Electrician[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl64, width: 130"]07/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]08/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]07/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Electrician[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl66, width: 130"]06/30/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]07/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]05/31/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Welder[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl66, width: 130"]05/31/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]06/30/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]06/30/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Marcelo Branco said:
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.
Click to expand...

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
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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

[TABLE="width: 832"]
<colgroup><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee ID[/TD]
[TD]Worker Type[/TD]
[TD]Contract Expiry Date[/TD]
[TD]Current Demob Date[/TD]
[TD]Comment[/TD]
[TD]Desired Demob Date (Calculated)[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1000[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]<strike>5/31/2020</strike>[/TD]
[TD]date to be used G15[/TD]
[TD]8/31/2019[/TD]
[TD]8/31/2019[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1001[/TD]
[TD]Electrician[/TD]
[TD]7/31/2019[/TD]
[TD]8/31/2019[/TD]
[TD]date used F2[/TD]
[TD]7/31/2019[/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1002[/TD]
[TD]Electrician[/TD]
[TD]6/30/2019[/TD]
[TD]7/31/2019[/TD]
[TD]date used F3[/TD]
[TD]9/30/2019[/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1003[/TD]
[TD]Electrician[/TD]
[TD]5/31/2020[/TD]
[TD]<strike>6/30/2020</strike>[/TD]
[TD]date to be used G21[/TD]
[TD]5/31/2020[/TD]
[TD]5/31/2020[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1004[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]5/31/2020[/TD]
[TD]date used F5[/TD]
[TD]1/31/2020[/TD]
[TD]8/31/2019[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1005[/TD]
[TD]Electrician[/TD]
[TD]1/31/2020[/TD]
[TD]<strike>4/30/2020</strike>[/TD]
[TD]date to be used G14[/TD]
[TD]4/30/2020[/TD]
[TD]1/31/2020[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1006[/TD]
[TD]Electrician[/TD]
[TD]7/31/2019[/TD]
[TD]<strike>9/30/2019</strike>[/TD]
[TD]date to be used G13[/TD]
[TD]6/30/2020[/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1007[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]<strike>5/31/2020</strike>[/TD]
[TD]date to be used G16[/TD]
[TD]#NUM![/TD]
[TD]8/31/2019[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1008[/TD]
[TD]Electrician[/TD]
[TD]7/31/2019[/TD]
[TD]<strike>8/31/2019</strike>[/TD]
[TD]date to be used G6[/TD]
[TD]#NUM![/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1009[/TD]
[TD]Electrician[/TD]
[TD]6/30/2019[/TD]
[TD]<strike>1/31/2020</strike>[/TD]
[TD]date to be used G7[/TD]
[TD]#NUM![/TD]
[TD]8/31/2019[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1010[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]<strike>5/31/2020</strike>[/TD]
[TD]date to be used G17[/TD]
[TD]#NUM![/TD]
[TD]8/31/2019[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1011[/TD]
[TD]Electrician[/TD]
[TD]7/31/2019[/TD]
[TD]<strike>8/31/2019</strike>[/TD]
[TD]date to be used G9[/TD]
[TD]#NUM![/TD]
[TD]9/30/2019[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1012[/TD]
[TD]Electrician[/TD]
[TD]5/31/2019[/TD]
[TD]<strike>7/31/2019</strike>[/TD]
[TD]date to be used G4[/TD]
[TD]#NUM![/TD]
[TD]4/30/2020[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1013[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]<strike>5/31/2020</strike>[/TD]
[TD]date to be used G18[/TD]
[TD]#NUM![/TD]
[TD]5/31/2020[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1014[/TD]
[TD]Electrician[/TD]
[TD]7/31/2019[/TD]
[TD]<strike>8/31/2019</strike>[/TD]
[TD]date to be used G11[/TD]
[TD]#NUM![/TD]
[TD]5/31/2020[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1015[/TD]
[TD]Electrician[/TD]
[TD]6/30/2019[/TD]
[TD]<strike>7/31/2019</strike>[/TD]
[TD]date to be used G8[/TD]
[TD]#NUM![/TD]
[TD]6/30/2020[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]1016[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]<strike>5/31/2020</strike>[/TD]
[TD]date to be used G19[/TD]
[TD]#NUM![/TD]
[TD]5/31/2020[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]1017[/TD]
[TD]Electrician[/TD]
[TD]7/31/2019[/TD]
[TD]<strike>8/31/2019</strike>[/TD]
[TD]date to be used G12[/TD]
[TD]#NUM![/TD]
[TD]5/31/2020[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]1018[/TD]
[TD]Electrician[/TD]
[TD]6/30/2019[/TD]
[TD]<strike>7/31/2019</strike>[/TD]
[TD]date to be use G10[/TD]
[TD]#NUM![/TD]
[TD]5/31/2020[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1019[/TD]
[TD]Electrician[/TD]
[TD]8/31/2019[/TD]
[TD]<strike>5/31/2020</strike>[/TD]
[TD]date to be used G20[/TD]
[TD]#NUM![/TD]
[TD="align: right"]5/31/2019[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Maybe this...
I think it's OK, but i found two different results (in yellow)


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


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.
 
Upvote 0
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.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Employee ID​
[/td][td]
Worker Type​
[/td][td]
Contract Expiry Date​
[/td][td]
Current Demob Date​
[/td][td]
Desired Demob Date (Calculated)​
[/td][td]
Date Used From​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
1000​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
08/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
1001​
[/td][td]
Electrician​
[/td][td]
07/31/2019​
[/td][td]
08/31/2019​
[/td][td]
07/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
1002​
[/td][td]
Electrician​
[/td][td]
06/30/2019​
[/td][td]
07/31/2019​
[/td][td]
07/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D14​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
1003​
[/td][td]
Electrician​
[/td][td]
05/31/2020​
[/td][td]
06/30/2020​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
1004​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
08/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D10​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
1005​
[/td][td]
Electrician​
[/td][td]
01/31/2020​
[/td][td]
04/30/2020​
[/td][td]
01/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D11​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
1006​
[/td][td]
Electrician​
[/td][td]
07/31/2019​
[/td][td]
09/30/2019​
[/td][td]
07/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D17​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
1007​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
08/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D13​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
1008​
[/td][td]
Electrician​
[/td][td]
07/31/2019​
[/td][td]
08/31/2019​
[/td][td]
07/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D20​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
1009​
[/td][td]
Electrician​
[/td][td]
06/30/2019​
[/td][td]
01/31/2020​
[/td][td]
08/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D16​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
1010​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
08/31/2019​
[/td][td="bgcolor:#D9D9D9"]
D19​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
1011​
[/td][td]
Electrician​
[/td][td]
07/31/2019​
[/td][td]
08/31/2019​
[/td][td]
09/30/2019​
[/td][td="bgcolor:#D9D9D9"]
D8​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
1012​
[/td][td]
Electrician​
[/td][td]
05/31/2019​
[/td][td]
07/31/2019​
[/td][td]
04/30/2020​
[/td][td="bgcolor:#D9D9D9"]
D7​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
1013​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D6​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
1014​
[/td][td]
Electrician​
[/td][td]
07/31/2019​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D9​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
1015​
[/td][td]
Electrician​
[/td][td]
06/30/2019​
[/td][td]
07/31/2019​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D12​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td]
1016​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D15​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td]
1017​
[/td][td]
Electrician​
[/td][td]
07/31/2019​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D18​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td]
1018​
[/td][td]
Electrician​
[/td][td]
06/30/2019​
[/td][td]
07/31/2019​
[/td][td]
05/31/2020​
[/td][td="bgcolor:#D9D9D9"]
D21​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td]
1019​
[/td][td]
Electrician​
[/td][td]
08/31/2019​
[/td][td]
05/31/2020​
[/td][td]
06/30/2020​
[/td][td="bgcolor:#D9D9D9"]
D5​
[/td][/tr]
[/table]

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.
 
Upvote 0
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.
 
Upvote 0
Marcelo Branco said:
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.


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


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.
Click to expand...

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...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
103
Peter_SSs
Peter_SSs
M
  • Question Question
How to sum cells in a column that match an End of Month date in an adjacent column
Replies
7
Views
249
Peter_SSs
Peter_SSs
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
139
SFCChase
SFCChase
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs
A
  • Question Question
SUMIFS
Replies
1
Views
314
Rijnsent
Rijnsent

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top