Hello -
I have a simple sheet with columns holding product, gross_margin, and rank. The rank is calculated using the Excel Rank formula. In a fourth column named ShelfLocation, I want to start allocating where the product will go on the shelf - low, middle or high. All of the products are in the same size case, so I have a very simple problem of getting X amount of shelf space filled with Y amount of product. The Low shelf is the premium shelf where I want to put my best margin items. Here is the problem...
Let's assume I have 32 cases of LOW shelf area. When I rank the product and sort by this Rank column I end up with 1,2,3... 29,30,30,31,31,31,32,32,33,33,33,33 etc. In otherwords the Rank delivers duplicate values. So my simple running balance forumla to assign LOW to a shelf over-allocates the shelf (my forumula read in part If (Rank <= 32, "Low", if...)).
The number of items and available shelf space is variable by store. So I am wanting a VBA solution that will loop through the sheet based upon Rank, and set the ShelfLocation cell based upon a maximum available shelf space, then filling the next shelf space to its maximum, and finally the top space with the remainder of items.
Any help providing a general outline of the code would be greatly helpful.
Thanks,
Steve
I have a simple sheet with columns holding product, gross_margin, and rank. The rank is calculated using the Excel Rank formula. In a fourth column named ShelfLocation, I want to start allocating where the product will go on the shelf - low, middle or high. All of the products are in the same size case, so I have a very simple problem of getting X amount of shelf space filled with Y amount of product. The Low shelf is the premium shelf where I want to put my best margin items. Here is the problem...
Let's assume I have 32 cases of LOW shelf area. When I rank the product and sort by this Rank column I end up with 1,2,3... 29,30,30,31,31,31,32,32,33,33,33,33 etc. In otherwords the Rank delivers duplicate values. So my simple running balance forumla to assign LOW to a shelf over-allocates the shelf (my forumula read in part If (Rank <= 32, "Low", if...)).
The number of items and available shelf space is variable by store. So I am wanting a VBA solution that will loop through the sheet based upon Rank, and set the ShelfLocation cell based upon a maximum available shelf space, then filling the next shelf space to its maximum, and finally the top space with the remainder of items.
Any help providing a general outline of the code would be greatly helpful.
Thanks,
Steve
