One Line Item into Multiple Based on Lookup

[blurryaf]

Hi,

I have lurked here for a while with things I've needed help with and have usually always found what I needed. This time, I have not and need some help.

I am looking to take line item(s) with a total quantity and break them out into multiple items with smaller quantities based on a lookup. The example here would be:

Item | Total Qty
Beverage 1 600
Beverage 2 300

This would then need to be broken out into multiple lines based on the lookup value of Beverage 1 = 100 per truck and Beverage 2 = 60 per truck.

Result:
Item | Qty per Truck
Beverage 1 100
Beverage 1 100
Beverage 1 100
Beverage 1 100
Beverage 1 100
Beverage 1 100
Beverage 2 60
Beverage 2 60
Beverage 2 60
Beverage 2 60
Beverage 2 60

I assume this will have to be done with VBA but I am still new to this type of coding. I have found examples here of VBA code that can do parts of this but have not found how to combine them all together.

Any help is greatly appreciated!

 

[AFPathfinder]

Welcome to the forums!.. the non-lurky part, anyway.

A formula can only change the value of a single cell, so this would have to be a VBA solution. The fact that all numbers aren't being split at 100 means there will have to be logic in the macro that accounts for which beverage gets split by which amount; could even be like a key table that identifies the quantities for each beverage. Is there a chance that the division might leave a residual that is less than the split amount?

Would you be looking for assistance in making the macro or for someone to make it for you?