Assign dates as late as possible without duplicates

[dfer43]

Hello,

I'm looking for a formula that will populate Column C below, using the list from Column E:

Screenshot 2024-08-09 123650.png

Column C must contain dates that:
Must be from Column E
Does not repeat (no duplicates)
Occurs prior to the date from Column B
Occur as late as possible, given the constraints above (major challenge is to produce as late as possible)

I have tried the RANK function, even assigning different duplicates to no avail.

Any thoughts?

[6StringJazzer]

Here is one solution attached, but I think we need a more rigorous specification for what you need.

If we start with the oldest date, and find the latest date before that, the answer is going to be optimal. It will be the best possible production date.

However, as we move down the line, we are going to find that the optimal date in some cases is already taken, so we have to pick an earlier date. So we get a solution that looks like this. When we try to get a date for production run 7, we have to reach all the way back to May 25 to get an available production date for a latest date of July 8. (See grid below.)

I have also provided a second solution that fills dates from latest to oldest, which gives more overall consistent results but still has some problems.

If that is not acceptable for business purposes, then you need to think about your optimizing criteria. Do you want a solution that gives the lowest average gap from actual production date to latest production date? Do you need a maximum gap?

If you need a more complicated optimizing algorithm then we probably need to solve this with VBA, or possibly using Solver (which can also be automated with VBA).

Values as displayed

	A	B	C	D	E
1	Production Run #	Latest Production Date	Actual Production Date ?		Potential Production Dates
2	1	6/10/2025	6/8/2025		3/26/2025
3	2	6/17/2025	6/16/2025		4/9/2025
4	3	6/23/2025	6/22/2025		5/3/2025
5	4	7/5/2025	6/20/2025		5/7/2025
6	5	7/5/2025	6/12/2025		5/11/2025
7	6	7/8/2025	7/6/2025		5/25/2025
8	7	7/8/2025	5/25/2025		6/8/2025
9	8	7/14/2025	7/10/2025		6/12/2025
10	9	7/17/2025	7/14/2025		6/16/2025
11	10	7/25/2025	7/18/2025		6/20/2025
12	11	8/14/2025	8/1/2025		6/22/2025
13	12	8/22/2025	8/19/2025		7/6/2025
14					7/10/2025
15					7/14/2025
16					7/18/2025
17					8/1/2025
18					8/15/2025
19					8/19/2025
Sheet1

[Phuocam]

Try this in C2:

Please Login or Register  to view this content.

[josephteh]

Try, in C2, copy down:
=IFERROR(XLOOKUP(B2-1,FILTER($E$2:$E$19,$E$2:$E$19<C3),FILTER($E$2:$E$19,$E$2:$E$19<C3),"",-1,-1),XLOOKUP(B2-1,$E$2:$E$19,$E$2:$E$19,"",-1,-1))

[dfer43]

Try this in C2:

Please Login or Register  to view this content.

Thank you! This formula worked perfectly!