Calculate best product mix - optimize order picking yield

[DKcrm]

Hi,

It's been some time since my last post on this board, but now I have new challenge. In short:
Is there a way in Excel to calculate the optimal product mix, so that as many orders as possible are fulfilled and at the same time, as many picking orders as possible goes above a certain picking minimum?

Background
I work in a logistics company, and have been assigned with the task of seeking to minimize the number shipments for the customers, thereby increasing the number of units going out in each shipment leading to minimizing the number of shipments to be handled under a certain minimum.
We are working in the apparel industry, where a large number of units on a fair number of product codes are to be processed within very short windows. Previously there has been a tendency to just ship to the customers when a certain number of product codes had been received into the warehouse, but now we want to consolidate more prior to shipping, and thereby minimize the number of picking/shipping batches within the shipping window, and at the same time try to mix the product codes so that we avoid as many minimums as possible and fulfill as many orders as possible.

Now, I have access to almost any data related to the products and sales orders, and I have prepared two data sets already in hope to be able to calculate the best mix of product codes.

There are some limitations and definitions.
* The minimim per picking order is 7 units. Picking Orders below 7 units will still be processed, but charged with a minimum handling fee.
* The maximum capacity in the picking area is 7.000 units. So the products to be handled in one batch should not exceed this figure
* Each product code will only be handled once. Once the product is processed, all sales orders should have been packed and shipped
* The 'Sales Order' table shows sales order details - Order ID is actually customer ID, and all shipments for the same customer ID are automatically consolidated in the picking job once products are put in the same batch
* The 'Stock data' table holds units in stock - nothing more will come. This volume is to be included in capacity calculations

Is it possible to calculate the optimal product mix based on the sales orders per product code?
VBA solutions are acceptable.
If this is not possible in Excel, is Access an option and if so, how?

Below tables are only to show the structure of the tables, and are not complete in this example.


Table one: Sales data
Product code, sales order, units

Book1
	L	M	N
1	Product Code	Order ID	Units sold
2	T28584179	CA - 53600/1001	6
3	T28584179	CA - 53600/1002	4
4	T28584179	D - 32030/1001	5
5	T28584179	TS - 07700/1001	24
6	T28584179	TS - 09000/1002	8
7	T28584179	TS - 09000/1003	7
8	T28584179	TS - 09000/1004	7
9	T28584179	TS - 09000/1005	7
10	T28584179	TS - 09000/1007	7
11	T28584179	TS - 09000/1008	7
12	T28584179	TS - 09000/1010	7
13	T28584179	TS - 09000/1011	7
14	T28584180	D - 31000/1001	8
15	T28584180	D - 32470/1001	24
16	T28584180	D - 32490/1001	8
17	T28584180	DA - 30110/3270	5
18	T28584180	SF - 63020/1001	56
19	T28584180	TS - 09000/1002	7
20	T28584180	TS - 09000/1003	7
21	T28584181	D - 32440/1001	4
22	T28584181	D - 32470/1001	12
23	T28584181	TS - 09000/1002	6
24	T28584181	TS - 09000/1003	6
25	T28584181	TS - 09000/1004	6
26	T28584181	TS - 09000/1005	6
27	T28584181	TS - 09000/1007	6
28	T28584181	TS - 09000/1008	6
29	T28584181	TS - 09000/1010	6
30	T28584181	TS - 09000/1011	6
31	T28584182	CA - 53600/1001	8
32	T28584182	CA - 53600/1002	7
33	T28584182	D - 32030/1001	5
34	T28584182	TS - 07700/1001	24
35	T28584182	TS - 09000/1002	8
36	T28584182	TS - 09000/1003	7
37	T28584182	TS - 09000/1004	7
38	T28584182	TS - 09000/1005	7
39	T28584182	TS - 09000/1007	7
40	T28584182	TS - 09000/1008	7
41	T28584182	TS - 09000/1010	7
Report 1

Table two: Stock data

Book1
	F	G
1	Product Code	Stock units
2	T28584179	171
3	T28584180	175
4	T28584181	97
5	T28584182	151
6	T28584183	165
7	T28584184	517
8	T28584185	24
9	T28584186	312
Report 2