• 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

    Hui...

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

Create a dynamic list based on multiple dynamic factors.

S

sabin348

New Member
Hey guys,
I spent hours trying and only have a swollen brain to show for it. Basically, I need to somehow create a list that can pull enough top ranking items to fill a given available space given keeping in consideration the different amount of space each item requires. Please see the attached file for more details.
Thanks,
 

Attachments

Hi:

I could not make out much from your excel file.

1.Is Finished list your expected list?
2.What do you mean by 36 bins?
3.What should be the starting point?
4. Is there any logical order in which you assign the values?

Thanks
 
Hi ,

If we sort the data in your input data range by the ranking , then the top-ranked items which add up to 72 are almost the same as what you have manually entered in the output range.

The only items are 40141 and 40180 where you have considered 2 bins each for what have been mentioned as HALF items ; the first point you need to clarify is the basis for deciding whether a HALF item is to be taken as 1 or 2 ?

Narayan
 

Attachments

Nebu said:
Hi:

I could not make out much from your excel file.

1.Is Finished list your expected list?
2.What do you mean by 36 bins?
3.What should be the starting point?
4. Is there any logical order in which you assign the values?

Thanks
Click to expand...

1. Yes, in this example the "Finished list would be what the results of the formula should look like.
2. In this scenario there are 36 available bins. Each bin can hold a maximum of two items which equals 72 available spaces. A FULL item always uses two spaces. A HALF item typically uses 1 space so usually you can get two items per bin. The problem is that a HALF can't share a space with a FULL so if there are an odd number of HALF's then one of them needs to be treated like a FULL and have it's own bin occupying 2 spaces. The other problem is that the items must remain in the order by description. If they are reordered by their rank then the grouping of HALF's and FULL's becomes incorrect in many cases.
3. First you have to determine how many spaces are available by looking at B4 Then pull out the highest ranking items from the Original Product List that will best fit the given space giving consideration to the space each item does or would require given the way it lays out in the finished list.
4. If you mean the rank values, they are predetermined by an outside factor. If you mean the HALF and FULL values then I think I answered it in question 2.
Thanks,
 
NARAYANK991 said:
Hi ,

If we sort the data in your input data range by the ranking , then the top-ranked items which add up to 72 are almost the same as what you have manually entered in the output range.

The only items are 40141 and 40180 where you have considered 2 bins each for what have been mentioned as HALF items ; the first point you need to clarify is the basis for deciding whether a HALF item is to be taken as 1 or 2 ?

Narayan
Click to expand...

First, the items have to remain in order of it's description which is the order the items are laid out in the Original Product List. A FULL item always requires 2 spaces or one FULL bin. A HALF bin typically requires a HALF bin meaning you need 2 HALF's to fill a bin. The problem is that when there is an odd number of HALF items in a group then the last HALF becomes requires 2 spaces because it can't share a bin with a FULL item.

Example FULL, HALF, HALF, HALF, FULL would require 4 bins or 8 spaces because the third HALF in that group would have to be by itself.

Example FULL, HALF, HALF, HALF, HALF, FULL would also require only four bins.

Example HALF, FULL, HALF, FULL is another that requires four bins.

Therefore the space requirements change based on the way the items would layout in the Finished List. Somehow I have to look at how many spaces are available, and how many of the top ranking items would fit based on their space requirements. In the example on the sheet there are 72 available spaces but only the top ranked 59 items actually fit.
Thanks,
 
You must log in or register to reply here.
Back
Top