Complex scheduling with recurrences

[UrbanS]

Hello,
I have a need to simulate jetty scheduling in excel (automatic scheduling by changing input data - number of customers, number of ships per year per customer - where different customers may have different number of ships). For a period of one year 104 ships will dock at the jetty. There will be 4 customers using the jetty. 1st customer will bring 11 ships, where the other 3 customers will bring 31 ships each. Between two consecutive ship arrivals at least 3 days must pass. Customers must also be split during the year equally meaning if a ship from customer A arrives, the next ship must be from an other customer and the next one from a third one and so on.
Appreciate any help,
Urban

 

[J.Ty.]

Hi,

Assuming the customers are A B C D, here is the order of ships arriving:

ABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCDABCABCABCD

It has 31 A, 31 B, 31 C, 11 D.

Schedule them every 3 days, which totals 324 days. You are left with 24 or 23 days, depending on the lear being leap or not. You can use insert them anywhere in the schedule.

J.Ty.

 

[UrbanS]

Hi J.Ty.,

thank you for the prompt reply. This would work as long as number of customers and ships stays unchanged, but the model should be able to adopt automatically by changing number of customers and/or number of ships per each separate customer as mentioned at the beginning of the question.

Best regards,
Urban

 

[J.Ty.]

Dear Urban,

OK.
The general algorithm is as follows:

1. Make a list of time slots, every 3 days, which gives 121 or 122 slots per year (column A).
2. Have a list of companies (A, B,...) in column C, each with number of its ships (column D).
3. First fill all odd numbered slots starting from 1, filling them first with all ships of company A, then all of B, and so on, until all odd slots are exhausted (formulas in column B).
4. Continue in the same manner, but filling even positions now and starting from slot 2 (formulas in column B).
5. Column E is with helpdata, to make computing ships easier.
6. Finally I have sorted the ships by slot numbers, to get the final schedul (columns G and H).
7. At the end you get a good schedule, and if two ships of the same company are positioned next to each other, it means that no schedule satisfying your requirements exists.

The model is made in Excel 365 with Array Functions - I hope you do have them. It is a smaller one, to make it fit on the screen.

J.Ty.

Book1
	A	B	C	D	E	F	G	H
1	Slot		Company	# of Ships	cumulative		ANSWER
2	1	A	A	2	2		SLOT	SHIP
3	3	A	B	2	4		1	A
4	5	B	C	3	7		2	C
5	7	B	D	4	11		3	A
6	9	C	E	1	12		4	D
7	11	C					5	B
8	2	C					6	D
9	4	D					7	B
10	6	D					8	D
11	8	D					9	C
12	10	D					10	D
13	12	E					11	C
14							12	E
Sheet4

Cell Formulas
Range	Formula
G3	G3	=SORT(A2:B13,1)
E2	E2	=D2
E3:E6	E3	=E2+D3
B2:B13	B2	=XLOOKUP(ROW()-1,$E$2:$E$6,$C$2:$C$6,"",1)

 

[J.Ty.]

My model is here, you can play with it.

J.Ty.

 

[UrbanS]

Thank you, very kind and helpful.

Urban

 

[J.Ty.]

Thank you for the feedback and good luck!

J.Ty.