FIFO distribution - VBA

[Glytch]

Hi guys

After trying to sort and search several solutions I had to post and ask for some help on how to aproach/solve the issue.

My file contains two sheets:
Sheet "Propriedades": has the entire product database (ItemID, PropertyValue1, QuantityReceived and other non relevant fields).
Its updated through a external data source every time it opens

Sheet "Import": has the import manual count of the products and show a total quantity number

The issue is: assign the Total Quantity (Sheet Import collumn D) to the individual ItemID considering the Property1 (date).
Criteria : It needs to be assigned from the most recent date to the oldest. The field QuantityReceived sets the maximum of quantities.

In the end from the total units counted and listed in the Import sheet there should be a assignment based on the recent date and limit of the Quantity received.

The data assignment can be done in a seperate sheet (no problems) whatever is easy.

Any ideas how I can aproach the issue?
I have included the attachment as example.

[Ed_Collins]

So to clarify, as an example, the end result would be these totals in Column E of the Import worksheet:

126
500
200
126
0
3
3
4
1

[Glytch]

Stock v.02.xlsmAs its not very easy to explain I have added a example of the expected outcome and the comments why/where the info comes from.

As for your question it has to work the other way around
Properties sheet contain the three fields of relevant data and needs to retrieve from the import only one
Property Sheet: ItemID, PropertyValue1,QuantityReceived
Import Sheet: ItemID(Collumn B) and StockUnits (collumn D)

ItemID: serves as reference between sheets - unique identity
PropertyValue1: date of the stock and mandatory. Sales deduct from the oldest lot.
QuantityReceived: maximum number of the lot
StockUnits: actual manual physical count

The distribution of the manual count then needs to be associated to the Properties (by date with a maximum never greater then the QuantityReceived)

Hope this makes it a litle more clear. If not please let me know.

Thanks

[Leith Ross]

Hello Glytch,

Please update your Personal Profile to include your location.

EDIT: Thanks updating your profile and adding your location.

[Glytch]

Any hints?

Thanks