Formula for automation restructuring

[dita87]

Dear all,

I am struggling with a case, where I need to re-arrange a lot of numbers automatically based on certain criterias.

For example:
We have orders (always with numbers 123..) that are differentiated between
1. Country -at the end of the order is the destination
U stands for USA
D stands for Dubai
2. Brand- X, Y, Z
3. Commodity-Shoes, Textiles

I receive number of pallets per each order, which I need to rearrange following the rules below:
1. Pallets cannot have more than 16 cartons
2. Different commodity groups cannot be mixed together
3. Different country destinations cannot be mixed together
4. Different brands with same commodity groups can be mixed together in the rare occasions that it will save space
5. Cartons can be broken down, but not unpacked, in order to be added to other pallets with less cartons


123U- Brand X-Shoes- 1 pallet, 12 cartons
234D- Brand X-Shoes-2 pallets, 23 cartons
567U- Brand X-Shoes-1 pallet, 3 cartons
789D-Brand X-Shoes-2 pallets, 17 cartons
100U-Brand Y-Textiles-1 pallet, 5 cartons
102D-Brand Y-Textiles-1 pallet, 8 cartons

From the example above, I will arrange them as follow
1. Order number: 123U+567U will be together, because they match criteria and make up to 15 cartons
2. Order number: 234D+789D will be also together, as follow:

• 234D- will stay alone with 16 cartons=23-16=7
• 789D- will stay alone with 16 cartons=17-16=1
• 234D+789D= 7+1=8

3. For the rest of the orders I will leave alone, because I have nothing to combine them with

In cases I have more orders with better combinations I always seek to optimize and get rid of pallets as much as possible.

So far, this task has been done manually in excel with just simple math without any formulas, due to its complexity.

However, if someone knows a smart way how this can be improved, I will super happy.

Many thanks in advance

-Dilyana