Complicated Queston

[LauraJustLaura]

I have a fairly new job as a Replenishment Analyst (buyer) and I'm having to manually calculate numbers because I'm not sure if excel can do these functions for me.
What I have:
- A spreadsheet that has:
1. Item numbers
2. Quantity currently on order
3. Pallet quantity (how many of the item make up a full pallet)
4. Weight of each item
5. Quantity to be ordered
6. Popularity of each item (A is highest, H is lowest)
- It already calculates:
1. Total weight (order quantity x weight)
2. Total number of pallets (order quantity / pallet quantity)

What I need to do with this info:
- I have to find a combination of parts that make up a container. A container must be:
> Exactly 20 pallets
> Under 37,000 lbs
> Can not contain more than 10 items that have a pallet quantity of 900

While;
- Prioritizing by
1. popularity (most popular is code A, least popular is code H)
2. quantity on order (lowest qty on order is highest priority)

Right now, I'm pretty much eyeballing it. I think Excel can group these parts in "container" quantities that meet the above criteria, but not sure how to make it do such a thing.

If anyone has any suggestions on how to make this process faster, I would greatly appreciate it!

 

[Legacy 143009]

If I were you I would sort by popularity and group by TOTAL PALLETS until I reach 20 pallets or 37000 lbs. Then I would distribute the leftovers.
Can you provide a sample file or BB2XL code to play with?