My company is shipping some parts to external paintshops. There is a number of paintshops and many part numbers.
I have a list with history of every move (shipped and returned).
My goal is to process the list so only those recently sent goods are on the list.
To explain it better, please see the example:
My list:
| Supplier | Part No. | Date | Quantity | Type |
|---|---|---|---|---|
| S01 | P0001 | 2024-05-05 | -3 | Received |
| S01 | P0001 | 2024-05-04 | 2 | Sent |
| S01 | P0001 | 2024-05-03 | -5 | Received |
| S02 | P0005 | 2024-04-24 | -6 | Received |
| S01 | P0001 | 2024-04-02 | 3 | Sent |
| S01 | P0001 | 2024-03-11 | 10 | Sent |
| S02 | P0005 | 2024-03-05 | 4 | Sent |
| S02 | P0005 | 2024-02-05 | 3 | Sent |
| S02 | P0001 | 2024-01-25 | -5 | Received |
| S02 | P0001 | 2024-01-14 | 5 | Sent |
Now, basing on this list, I want to keep those records of “Sent” type, which are still at supplier (didn’t returned yet). And difficult part is, that not all sent quantity needs to be returned, which means that particular quantities need to be reduced by those already returned (result in separate column “Balance”).
Expected result:
| Supplier | Part No. | Date | Quantity | Balance |
|---|---|---|---|---|
| S01 | P0001 | 2024-05-04 | 2 | 2 |
| S01 | P0001 | 2024-04-02 | 3 | 3 |
| S01 | P0001 | 2024-03-11 | 10 | 2 |
| S02 | P0005 | 2024-03-05 | 4 | 1 |
Explanation:
The oldest shipped part P0001 to supplier S02 has been returned, hence two last record will be removed.
Part P0005 sent to supplier S02 in quantity 3 and then another 4, however 6 was returned later on. Considering “first out first in” rule, there are still 1 left at supplier S02, hence the 4th line in result table.
Consequently, part P0001 sent to supplier S01 (this has to be independend from those shipped to S02) in quantity of (10+3+2) and received painted (5+3). This returned quantity has to be deducted from first shipment, while other two shipments are still at supplier. Therefore in line 3 of result table the balance is (10-5-3=2) while in line 1 and 2 other two shipments will stay as they were.
My first attempt is to use Power Query, but I can’t figure out how to do that. Is it even possible?
If not, my next attempt will be VBA, but I really hope PQ can do that task.