I currently have a list of stock in a table (in sheet2). The table is sorted by column a then column e, so all same stock codes are together and then the highest to lowest numbers of stock per bin are shown.
Column A contains product code,
Columns b-d are various descriptions
column e shows how many of the product are stored in a bin.
There are many cases in the list where the stock is in more than one bin and can be consilidated into just one bin. Depending on the stock, it can be spread over up to 20 bins.
I want the code to produce a list for me (in sheet3) that will show what bins can be consilidated. This will save me going through a list manually which can be up to 8000 lines long.
So establish the max amount of stock of a certain item a bin can hold, the code would have to look for the maximum value in column e per stock code(column a).
Once this is done, all bins that contain 50% or less stock than the max amount should be copied into the new list.
If there are any cases where the stock can not be consolidated (ie, 2 bins of stock, each with 2 items in each bin, or 3 items in a bin and 1 or 2 in another) then these should be removed from the list.
Is this possible? If it is, would someone be kind enough to write it for me as this is way beyong my meager knowledge of VBA. Thanks
Column A contains product code,
Columns b-d are various descriptions
column e shows how many of the product are stored in a bin.
There are many cases in the list where the stock is in more than one bin and can be consilidated into just one bin. Depending on the stock, it can be spread over up to 20 bins.
I want the code to produce a list for me (in sheet3) that will show what bins can be consilidated. This will save me going through a list manually which can be up to 8000 lines long.
So establish the max amount of stock of a certain item a bin can hold, the code would have to look for the maximum value in column e per stock code(column a).
Once this is done, all bins that contain 50% or less stock than the max amount should be copied into the new list.
If there are any cases where the stock can not be consolidated (ie, 2 bins of stock, each with 2 items in each bin, or 3 items in a bin and 1 or 2 in another) then these should be removed from the list.
Is this possible? If it is, would someone be kind enough to write it for me as this is way beyong my meager knowledge of VBA. Thanks