Calculate pallet configuration from many different products

Hi, I have been trying to work out the following for some time now without too much success. This is the problem:

We ship all our products on pallets, fortunately most of the product are packed in bales of the same size. The number of products per bale is different and I have worked out a way of determining the number; the weight of each bale is also different and again I have worked out a way of calculating the total weight. I know the pallet takes a maximum seven layers of five bales per layer making a total of thirty five bales per pallet and a maximum weight of 1000kgs.

What I would like excel to do is automatically determine a packing configuration for each pallet based on the customer order and produce a packing slip for each pallet of the products on that particular pallet.

For example:

30 bales of Product A each bale is 30kg
25 bales of Product B each bale is 20kg
20 bales of Product C each bale is 40kg
40 bales of Product D each bale is 10kg

So Pallet 1 will have 30 bales of Product A = 900kg
and 5 bales of Product B = 100kg Totaling 1000kg and the max 7 layers.
and so on until all product is on a pallet.
Is this possible within excel?
Many thanks in advance for your help

Thanks RoyUK, I wasn't aware that there was such an add-in could prove very useful.