Create a dynamic list based on multiple dynamic factors.

Hey guys,
You've solved my serious headaches in the past I'm hoping you can do it again. 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,

Originally Posted by Jacc

How come some HALF take up two spaces?

A HALF can't be in the same bin as a FULL therefore depending on where the item is in the order of items a HALF may need to be in a bin by itself. For example, if there are three HALF 's in a row and one FULL then two HALF's would share a bin, one HALF would have it's own bin and the FULL would have it's own bin, if there are four HALF 's in a row and a FULL then it still uses only three bins.

The product list, as you say, can change. However, you seem to have arranged the products into their bins by order of the product list (i.e. from top to bottom). The way I see it, depending on how you arrange the product list you have a different solution each time, even with the same items) just because of how the HALFs and FULLs are arranged from top to bottom.

Is this list fixed the way it is now, or can they be re-arranged as long as it fits the 72 bins you require?

The reason why I'm asking this is because the only way I can see it done using formulas would be to re-arrange the list according to their ranking.

If you look at the the description of the items you'll see that they are actually sizes of product. This product list will later be represented as part of a display. The problem with shuffling them into the order of their rank is that the bin configuration will go out of wack. For example if you had six items in order of description that required FULL, HALF, FULL, HALF, HALF, HALF it would require five full bins because the first HALF would need to be by itself and the next two HALF's could share a bin while the last HALF would also be in a bin by itself. However, if the same products were reorganized by rank and it instead laid out as HALF, HALF, HALF, HALF FULL, FULL, for example, it would only need four drawers which wouldn't be the correct number of bins because the products have to be laid out in the order of their description size.

The purpose of this program that I'm working on is to fit in as many of the top performing items that fit into a given display size. So if I have only 72 available spaces (36 bins) then I have to make sure I put in as many of the highest ranking items as possible given the available space. Please keep in mind that the finished product does not have to be accomplished in one step. It can be as many steps needed to accomplish the task.

yes, it needs to keep in consideration the highest ranking items

Originally Posted by quekbc

Is this list fixed the way it is now, or can they be re-arranged as long as it fits the 72 bins you require?
The reason why I'm asking this is because the only way I can see it done using formulas would be to re-arrange the list according to their ranking.

Since I've not received a response on this, I'm just gonna post a possible solution here after sorting the product list by rank. (or see attached file)

Row\Column	I	J	K	L	M
4	Finished List
5	Item#	Description	Rank	Configuration
6	40015	#6 x 3/4	1	HALF	1
7	40060	#8 x 3/4	2	HALF	2

Formula in I, J and L7

Formula:

Please Login or Register  to view this content.

Formula in K7

Formula:

Please Login or Register  to view this content.

Formula in M7

Formula:

Please Login or Register  to view this content.

Originally Posted by quekbc

Since I've not received a response on this, I'm just gonna post a possible solution here after sorting the product list by rank. (or see attached file)

Row\Column	I	J	K	L	M
4	Finished List
5	Item#	Description	Rank	Configuration
6	40015	#6 x 3/4	1	HALF	1
7	40060	#8 x 3/4	2	HALF	2

Formula in I, J and L7

Formula:

Please Login or Register  to view this content.

Formula in K7

Formula:

Please Login or Register  to view this content.

Formula in M7

Formula:

Please Login or Register  to view this content.

I'm sorry, it can't be rearranged by order of rank. It must stay in order of description. Here's what I posted earlier that might help explain why.

If you look at the the description of the items you'll see that they are actually sizes of product. This product list will later be represented as part of a display. The problem with shuffling them into the order of their rank is that the bin configuration will go out of wack. For example if you had six items in order of description that required FULL, HALF, FULL, HALF, HALF, HALF it would require five full bins because the first HALF would need to be by itself and the next two HALF's could share a bin while the last HALF would also be in a bin by itself. However, if the same products were reorganized by rank and it instead laid out as HALF, HALF, HALF, HALF FULL, FULL, for example, it would only need four drawers which wouldn't be the correct number of bins because the products have to be laid out in the order of their description size.

The purpose of this program that I'm working on is to fit in as many of the top performing items that fit into a given display size. So if I have only 72 available spaces (36 bins) then I have to make sure I put in as many of the highest ranking items as possible given the available space. Please keep in mind that the finished product does not have to be accomplished in one step. It can be as many steps needed to accomplish the task.