Custom sorting?

[adrianpuscsu]

Hello,

I am trying to find a solution to automatically optimize a production plan.
This is the available data:
1. Factory X is able to manufacture the following semi-product: A, B, C, D, E, F, G, H, I, J, K, L & M.
2. According to the daily requirement of the final product, factory X should produce the semi-products like this:

test.xlsb
	A	B
1	17/11	C
2	17/11	E
3	17/11	H
4	17/11	I
5	18/11	B
6	18/11	G
7	18/11	I
8	18/11	I
9	19/11	H
10	19/11	I
11	19/11	I
12	19/11	M
13	20/11	G
14	20/11	M
15	21/11	I
16	21/11	I
17	22/11	B
18	22/11	I
19	22/11	I
20	22/11	I
21	23/11	B
22	23/11	E
23	23/11	G
24	23/11	G
25	24/11	F
26	24/11	F
27	24/11	I
28	24/11	I
29	25/11	B
30	25/11	I
31	25/11	I
32	25/11	I
33	26/11	G
34	26/11	I
35	26/11	I
36	26/11	M
Sheet1

3. What I am trying to achieve is to decrease the number of semi-product changes, while not moving the production from one day to another. The only limitation is that the factory can manufacture one product in a sequence of maximum 4 batches. Therefore, the results should look like this:

test.xlsb
	D	E
1	17/11	C
2	17/11	E
3	17/11	H
4	17/11	I
5	18/11	B
6	18/11	G
7	18/11	I
8	18/11	I
9	19/11	I
10	19/11	I
11	19/11	H
12	19/11	M
13	20/11	M
14	20/11	G
15	21/11	I
16	21/11	I
17	22/11	I
18	22/11	I
19	22/11	I
20	22/11	B
21	23/11	B
22	23/11	E
23	23/11	G
24	23/11	G
25	24/11	F
26	24/11	F
27	24/11	I
28	24/11	I
29	25/11	I
30	25/11	I
31	25/11	B
32	25/11	I
33	26/11	I
34	26/11	I
35	26/11	G
36	26/11	M
Sheet1

Can you please help me with an any idea on how I could achieve something like this?

Thank you!

 

[adrianpuscsu]

Hello,

I am trying to find a solution to automatically optimize a production plan.
This is the available data:
1. Factory X is able to manufacture the following semi-product: A, B, C, D, E, F, G, H, I, J, K, L & M.
2. According to the daily requirement of the final product, factory X should produce the semi-products like this:

test.xlsb
	A	B
1	17/11	C
2	17/11	E
3	17/11	H
4	17/11	I
5	18/11	B
6	18/11	G
7	18/11	I
8	18/11	I
9	19/11	H
10	19/11	I
11	19/11	I
12	19/11	M
13	20/11	G
14	20/11	M
15	21/11	I
16	21/11	I
17	22/11	B
18	22/11	I
19	22/11	I
20	22/11	I
21	23/11	B
22	23/11	E
23	23/11	G
24	23/11	G
25	24/11	F
26	24/11	F
27	24/11	I
28	24/11	I
29	25/11	B
30	25/11	I
31	25/11	I
32	25/11	I
33	26/11	G
34	26/11	I
35	26/11	I
36	26/11	M
Sheet1

3. What I am trying to achieve is to decrease the number of semi-product changes, while not moving the production from one day to another. The only limitation is that the factory can manufacture one product in a sequence of maximum 4 batches. Therefore, the results should look like this:

test.xlsb
	D	E
1	17/11	C
2	17/11	E
3	17/11	H
4	17/11	I
5	18/11	B
6	18/11	G
7	18/11	I
8	18/11	I
9	19/11	I
10	19/11	I
11	19/11	H
12	19/11	M
13	20/11	M
14	20/11	G
15	21/11	I
16	21/11	I
17	22/11	I
18	22/11	I
19	22/11	I
20	22/11	B
21	23/11	B
22	23/11	E
23	23/11	G
24	23/11	G
25	24/11	F
26	24/11	F
27	24/11	I
28	24/11	I
29	25/11	I
30	25/11	I
31	25/11	B
32	25/11	I
33	26/11	I
34	26/11	I
35	26/11	G
36	26/11	M
Sheet1

Can you please help me with an any idea on how I could achieve something like this?

Thank you!

Sorry, I just realized that maybe more information is needed.
1. In this example, the factory has 2 production lines
2. One batch takes 12 hours to complete, therefore the factory can produce 4 batches in 24 hours.
3. The "original" data is calculated using VBA to connect to SAP for exporting the current semi-product stock + the requirement from a different Excel file.
I am just missing an automatic solution for distributing the semi-products manufacturing, taking into account the limitations.

Thanks again!