Index / Match / lookup to give the 1st, 2nd, 3rd, etc value from a list

[russthedutch]

Hello,

I'm really struggling with this one. I have a dataset which lists item barcodes and then a bunch of storage slots in warehouse where these items live, and will be picked from. I need to build a manual pick sheet for contingency which we can extract the data, dump into a template and give locations to pick from. Here is an example of my dataset. (sorry its a bit out of line)

Barcode Qty Req Plt Qty EAN Pick From Pick Qty
111111 30 10 111111 Slot 1 10
222222 20 10 111111 Slot 2 10
333333 50 10 111111 Slot 3 10
444444 8 2 222222 Slot 4 10
555555 30 15 222222 Slot 5 10
666666 15 15 333333 Slot 6 10
777777 27 9 333333 Slot 7 10
333333 Slot 8 10
333333 Slot 9 10
333333 Slot 10 10
444444 Slot 11 2
444444 Slot 12 2
444444 Slot 13 2
444444 Slot 14 2
555555 Slot 15 15
555555 Slot 16 15
666666 Slot 17 15
777777 Slot 18 9
777777 Slot 19 9
777777 Slot 20 9

So, the first three columns list what items need to be picked, and the quantities that are required. The other three columns are my desired outcome.

Basically if item 111111 requires 30 to be picked, I would like a formula to find all slots related to the item, look at the pallet qty, in this case 10, and then pick one pallet from slot 1, then if the total required has not yet been reached, move to slot 2, then slot 3 until the total amount to pick has been reached. Then move to the next item code 222222.

I'm really puzzled with this one. Usually I can work these things out, but this one I can't. The objective is to reduce the amount of time it takes to prepare a manual pick sheet, so ideally, I'd like to populate a template and let a formula do its work, without manual copy/paste which can result in errors.

Thanks,

Russ