• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identifying packaging units based on certain criteria

I have been trying to figure out a way by which I would be able to identify a number of packaging boxes based on surface volume, that will fit into a bigger box i.e how many of the smaller boxes (if one calculates by surface volume) would fit in a bigger box with a certain surface volume. Accordingly, the matrix will identify the smaller boxes that will go into Box 1, 2, 3 etc...

Data :
1. In cell range K2:N6 are mentioned the dimensions of some packing boxes.
2. In Column o, the volume for each box is mentioned by multiplying LxBxH.
3. The packing boxes either need to fit into a bigger box of category "cold chain" or "normal".
4. The volume of the bigger boxes (in which the smaller boxes would go in) are mentioned in the column P &Q according to categories. Same depicted in the data in Col: F
5. In the column A - I are mentioned some invoice numbers, corresponding with customer names, Item Names
6. Against items, are mentioned in which type of box they should be packed (Col: E)
7. Against each box, the Packaging Type (Cold Chain/Normal) is also mentioned
8. In column "G" is mentioned the quantity of boxes required to pack the item
9. Column H shows the total volume by multiplying Quantity of boxes with the volume of each type of box

What is required is:

1. The formula should check for the "Packaging Type" (Col F) accordingly sum up the volumes projected in Col: H
2. Second criteria is that once the volume (mentioned in Col: P3 & Q3) of the particular "packaging Type" (Cold Chain/Normal) is achieved by summing the volumes of the identified boxes in Col:H, all the corresponding cells in Col:I should be populated by a serial number ( here it is shown as 1,2,3...etc. signifying "Packaging Type" box numbers). You may take it as "Units" which comprises of a collection of boxes.
3. Cold Chain Units should have serial starting from 1 and the same should happen with the "Normal" packaging type.
4. If the Packaging Type is "Cold Chain" and falls into the 1st unit of its type, then the corresponding cell in Col : I should populate 1 till the end of the range of boxes that will fit in 1 unit of the particular "Packaging Type" post that it should start calculating for the next Unit i.e Unit 2and the same should hold good for the "Normal" ...and the series should go on

I understand that the issue is tricky and might be that I am deficient in portraying the issue properly. I would request you all to kindly go through the text and the sample file and if there is any question, please let me know. I am attaching a sample for your perusal & understanding.