Excel 2016: Creating Formula for Allocating Cartons to Pallets in Delivery Acceptance System

Hello. I am asking you for help in creating a formula that will allocate cartons to pallets. Starting from the beginning, in the attachment I am sending an Excel file with a simplified table "my work" on the delivery acceptance system. I presented there a solution that interests me and I would like to focus on this table. The data I have is the number of boxes that were in the delivery and so, for example, article No. 1 came in 20 boxes. Due to the size, one pallet can contain 10 cartons, therefore there will be 2 full pallets. And there is no problem with that. The problem appears when items in the amount of incomplete pallets arrive, e.g. article number 6 (12 boxes) and 8 (28 boxes). Apart from full pallets, there are 2 and 8 "loose" boxes. And here the problem arises ... in the example it is 10).

I hope that I have explained what my problem is

Katline wrote:

... so that the program is able to assign me a pallet number by combining different articles so that the number does not exceed the maximum number of cartons on a pallet (in the example it is 10) ...

Are there any additional criteria for the allocation of 'leftovers' that would 'mark' which items / item pairs can be combined?
I am asking because there can be many such-tens, not only those marked in the example file, for example:
Różne ukła...rtonów.jpg Download (59.82 kB)

Of course, there are many combinations and it does not matter which items will end up on one pallet, as long as you do not unnecessarily break one type into many pallets. I realize it won't necessarily be easy, so I have a big problem with that. I don't even have an idea where to start, what formulas can help here, but I believe in Excel's ability that such calculations are also possible

Katline wrote:

Of course, there are many combinations and it does not matter which items will end up on one pallet, as long as you do not unnecessarily break one type into many pallets.

If we assume the distribution of the "FIFO" type, then for the example data and assumptions, the solution can be obtained as in the screenshot:
2017-05...png Download (26.37 kB)

by using "formula table" and conditional formatting shown in the attached file

Prot tes...xlsx Download (15.41 kB)Points: 1.5 for user

I have been working on the problem for several days. At first I did not realize how difficult the task I had set before myself. The longer I sit, the more different ways I try to bite, the more it seems to me that I will not even get closer to the goal, and as I am a beginner in Excel, it makes it more difficult for me to look for a solution :D

Prot ... I am very impressed, because you managed to get closer to what I would like to achieve Nevertheless, your solution is also not perfect and while there would be no problem with writing it out into a larger number of boxes and pallets, the problem arises when there are many boxes in small quantities. And just like in the photo, your method "threw" 13 cartons onto the palette no. 12 (I know that it is the fault of adding up a small number of cells).


Ehh ... I have the impression that I will not do this calculation with the help of Excel
Ultimately, it was supposed to look like that when there is a delivery, it consists in randomly picking up all the cartons and it would be ideal to sort them immediately by types on pallets, so the program would have to "arrange" everything on the pallets in advance according to the delivery data (article number, quantity cartons, number of cartons on a pallet).

Regards Prot for coming up with this chart though, I'm amazed

Katline wrote:

the problem arises already when there are many boxes in small quantities.

In the presented example you presented my friend the worst option of "picking up on a pallet" of cardboard boxes three assortments - hence the formulas are prepared only for such conditions .

Formulas, of course, can be extended by nesting successive IF () conditions - but three levels seemed sufficient to me and hence these functional limitations :cry:

However, I practically cannot imagine the use of special calculation sheets to determine the distribution of assortments on pallets, as all assortments will be delivered 1 carton - you need to load another 10 assortments per pallet and a receipt

How is it with "three assortments"? In my example, there was an "Item #" column from 1 to 10 - by default I figured there are 10 types of items. In addition, each of these types came in a certain number of boxes. Art no. 1 - 20 boxes, art. No. 2 - 30 boxes .... Art. No. 10 - 6 boxes.


Thus, it is possible to deliver up to 50 different TYPES of articles, packed in cardboard boxes. For example, one article will only have 1 carton, so it will be combined on a pallet with other articles, but another article may have a total of 5 pallets (i.e. 50 cartons). The division of delivery into pallets would be such a distribution of cartons to minimize the occurrence of such a situation that, for example (referring to shoes) - I would have a size of 40, one carton on five different pallets, and I would like all 5 cartons to be on one pallet because it is the same product and you would not have to look for it all over the warehouse.

Katline wrote:

How is it with "three assortments"?

This is what your sample data shows - only on the palette 12 and 15 there is a connection situation "three assortments" ;-)

You can see it in the screenshot:

rozkar.JPG Download (85.16 kB)

You can see here (or preferably in the previously attached file ) that according to the pre-defined needs of the formula in column G. they "check" only the above two "leftovers" from column M.

Adjusting the sheet to real conditions therefore requires modification of these formulas in column G. for checking a larger number of "leftovers" or for pre-sorting assortments, so that "single" boxes do not accumulate into groups, e.g. for your second version of data:

rozkar..JPG Download (63.27 kB)

Katline wrote:

... I have the impression that I will not do this calculation with the help of Excel ...

I will, I will ... except that my friend set a tedious task for herself ... ... but don't get discouraged ... it's all about
that it is not the subject that 'murdered' us, but that we 'finished off' this subject ... ...

An example - almost untested, only on three chips - if it is useful for anything, it's good ... if not ... then not ... ...
test2_..zip Download (15.18 kB)Points: 1 for user

Assumptions made for the example:
1. No more than two different assortments on a pallet.
More than two more complicate the matter, as it would be possible to compare successively increasing sums of searched articles
with the number of consecutively added ones, so that their total number does not exceed the assumed criterion.

2. Simplifying the search criteria for the number of cartons to cover one pallet.
No penetration, whether the arrangement of cartons on one pallet = 2 + 3, and on the other = 3, with the following values: 2, 3, 3 - is better than the arrangement of cartons
on one pallet = 2 and on the other = 3 + 3, ie the subsequent qualifications for the allocation of cartons to a pallet are "positional" and not "quantitative".

3. As short as possible procedure code based on comparisons of numbers.

Ps:
Making this issue a "program to analyze" the distribution of cardboard boxes on a pallet is not impossible, but tedious,
requiring time and the so-called "thought effort" ... whatever that means ... ...

lanzul, it looks very nice, unfortunately my concept of creating a macro is very modest, so at the moment I can't handle it, although I would like to keep the file for later I received quite a lot of cool solutions, I will try to put something together to make my work a little easier. Thank you for all your help and I'm finishing the topic for the moment