Solver or Formula or Macros

Dear excel experts,

Please see attached sample workbook.

This is a drug manufacturing factory which makes devices container Drug A +/- Drug B. The device type could be type I or type II.
I would like to use excel Solver (or Formula or Macros if there is an alternative) to solve an optimal output in F2:H21
in F2:H21 can excel output the number of each device to be manufactured on Day 1,2 and 3? The demand of each individual device is listed in column D.

Constraints (in order of priority):
- by end of Day 3, all demand (column D) are fulfilled
- Number of available drug A per day for manufacturing is 500 grams max
- The number of devices to be manufactured should be distributed as evenly as possible across Day 1 to 3
- Ideally manufacturing of a particular device must be finished within a day i.e. the manufacturing of a line cannot be spreaded across more than 1 day
- it is desirable to group as many Type I devices together to be manufactured on the same day as possible; Type II devices likewise
- it is desirable to group as many devices containing Drug B together to be manufactured on the same day as possible

Thanks!

It appears there are four combinations as follows:

Drug B Y/N	Device I or II
Y	I
Y	II
N	I
N	II

If you ad a helper column to calculate the grams of Drug A (Demand X Dose) you could use a pivot table to determine the total grams of Drug A for all combinations.

I did this in the attached and then put IF statement formulas in columns F & G as required in the attached workbook.