How to manage imported and distributed items|Power Query|Excel Forum|My Online Training Hub

Hi, I need some assistance please.

We import products that are then distributed to customers.  The list that is currently used (see attached example), shows the quantity imported (Qty) and the duty paid (Duty).

When Items are sold (Used), it is indicated on the sheet by adding a row with the status of Used.  All Used items must be equal to the Imported items based on Line number, Product code, Invoice Number, BE date and BE number. The distributed (Used) items are not always equal to the Import qty, and it might take 2 or more lines to match up to the Import quantity.

For row 13 there were 560 items imported, but nothing used or distributed yet.

Products must be distributed (Used) chronologically, so first in, first out.

The problem is that this list can consist of 7 000 or more rows and to find the next available product becomes a challenge.

Is there a way to perhaps filter out/remove from view, where the Import and Used lines add up to the same quantity for the same Line Number, Product Code, Duty, Invoice number, Customs BE date, Customer BE Number and Qty?

We also need to calculate the pro-rata duty for the Used items (see Column E > used items only)

Any assistance on how to manage this in Power Query would be greatly appreciated.