Some pretty itensive code needed

[Hozz]

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

 

[Gerald Higgins]

Hi Hozz

I don't know much about VBA, so I can't help you on that (but others here will probably be able to).
But I wonder if alot of it can actually be done by formulas, and use of pivot tables / data filter etc.

I'm not completely clear exactly what you want - it might help to explain more.

For example, when you say

I want the code to produce a list for me (in sheet3) that will show what bins can be consilidated

how exactly do you decide which bins can be consolidated ?

 

[Geoff Taylor]

The Subtotal feature has a MAX option. Run that, and then in a new column enter the formula
=IF(VLOOKUP((A4&" Max"),$B$3:$E$8000,4,FALSE)*0.5>E4,"Consolidate","No")

Then filter on that new column, copy and paste in your new sheet.

There may be better ways of doing this, but this'll work.

 

[Hozz]

Gerald, if there are 8 bins in total, and one of the bins contains 50 items and the others contain 50, 20, 20, 10 then it should use 50 as the max that can be fit into a bin and so, half the max in a bin should be what should be consilidated. In this case, it would be the two 20's and the 10

 

[Gerald Higgins]

Now I'm even more confused.
How does "half the max in a bin" (in this case 25, as half of the max of 50) reconcile with consolidating the two 20s and the 10 ?

I understand how, if 50 is the max, then the two 20s and the 10 can be consolidated into one, but don't see the relationship to "half the max".
What if one bin had more than half the max, say 30, would you want to consolidate that with either a 10 or a 20 ?

Also, what if, in your example, 50 was the max for a bin, but you just happened to have no bins that actually had 50 - let's say you had 45, 30, 20 and 10. How would you identify that 50 was actually the max ? Is this information stored somewhere ? Is this what's in Col E ?

 

[Hozz]

Hi again,

Your confusing matters by adding extra difficulty.
The list will change regularly as stock is always going in and out. This will never be a perfect system, but it's a small timesaver.
The maximum a bin can hold will be determined by the maximum number in a bin in the list.
So in your example, the maximum would be 45. so, half of this, rounded up would be 23. so any bins with 23 or less items in can be consolidated.

As I said, it's not a perfect system, but when the storage area here holds 20,000 bins, and there can be up to 10,000 differant products in there, there will always be some consolidation to be done.

 

[Gerald Higgins]

OK. Here's a quick and dirty way, assuming you can sort the list first by product, then by descending order of quantity.

In Col F, put this formula (I'm assuming your data starts on row 2)

=IF(A2=A1,+F1,+E2)

and copy down as far as required.
This will copy down the max value for each product all the way down the list.

Then in Col G put this formula

=IF(E2<ROUND(F2/2,0),"CONSOLIDATE","DON'T CONSOLIDATE")

and copy down.

One problem with your approach is that it might miss some consolidation opportunities, if you have lots of bins for the same product, and all of them are less than half full. In my opinion, a better way might be to have a master list of all products and the maximum amount for each of them that can be held in a bin, and then compare all the actual quantities against that. But maybe this isn't an issue for you.

Good luck !

 

[Gerald Higgins]

Sorry Hozz, having problems editing my post. The second formula should be

=IF(E2<ROUND(F2/2,0),"CONSOLIDATE","DON'T CONSOLIDATE")

 

[Gerald Higgins]

Hmm. . . don't know what's going on today.

Hozz, I've got the second formula working on my system, but I just can't seem to get it to post properly.

Here goes again

=IF(E2<ROUND(F2/2,0),"CONSOLIDATE","DON'T CONSOLIDATE")

 

[Gerald Higgins]

I'll try PM'ing it to you