Spreadsheet: 876915d1723636037-inventory-status-report-inventory
Sheet: Sheet1

I am requesting an Excel formula to generate an inventory status report as shown on the desired solution, utilizing data from both the Blend_P and Blend_S tables.
The report should prioritize displaying purchases ahead of sales and should culminate in the recording of the actual balance at the close of each day.
Desired Solution |  |  |  |  |  |  |  |  |  |  |  | Purchases |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Purchases
 |  |  |  |  |  |  |  |  |  |  |  | Date | Reference | Ordered | Unit Price | Litres | Actual Balance |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Date | [NAME] | Driveway | Litres | Unit Price | Amount | Actual Balance | Ordered |  |  |  |  |  |  |  |  |  |  |  | Date | [NAME] | M / Invoice | Invoice | Delivery Note | Ordered | Unit Price | Amount | Litres | [NAME] | Actual Balance
Date | Reference | Ordered | Purchase Price | Sales Price | Purchases (Litres) | Sales (Litres) | Balance | Actual Balance | [NAME] |  |  |  |  |  |  |  | 6825 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-04 00:00:00 | Blend | 73125 | 2357.9 | 1.72 | 4055.588 | 4470.1 |  |  |  |  |  |  |  |  |  |  |  |  | 45295 | Blend | 0 | B/FWD | 0 | 6825 | 1.677 | 11445.525 | 0 | 0
2024-01-04 00:00:00 | B/FWD |  |  |  |  |  | 6825 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-05 00:00:00 | Blend | 73126 | 1900.7 | 1.72 | 3269.204 | 19140.9 |  |  |  |  |  |  |  |  |  |  |  |  | 45296 | Blend | 44487 | FHIL00001286 | 35035 | 16600 | 1.677 | 27838.2 | 16613.5 | 13.5
2024-01-04 00:00:00 | 73125 |  |  | 1.72 |  | 2357.9 | 4467.1 | 4470.1 | -3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-06 00:00:00 | Blend | 73128 | 2730.6 | 1.72 | 4696.632 | 16469.3 |  |  |  |  |  |  |  |  |  |  |  |  | 45299 | Blend | 0 | FHIL00001296 | 35138 | 16600 | 1.677 | 27838.2 | 16619.5 | 19.5
2024-01-05 00:00:00 | FHIL00001286 | 16600 | 1.677 |  | 16613.5 |  | 21080.6 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-07 00:00:00 | Blend | 73129 | 2619.82 | 1.72 | 4506.0904 | 13819.48 |  |  |  |  |  |  |  |  |  |  |  |  | 45326 | Blend | 0 | FHIL00001285 | 35260 | 10000 | 1.817 | 18170 | 9982.2 | -17.79999999999927
2024-01-05 00:00:00 | 73126 |  |  | 1.72 |  | 1900.7 | 19179.9 | 19140.9 | 38.99999999999636 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-01-08 00:00:00 | Blend | 73132 | 3254.01 | 1.72 | 5596.8972 | 27194.97 |  |  |  |  |  |  |  |  |  |  |  |  | 45361 | Blend | 0 | FHIL00001285 | 35260 | 10600 | 1.41 | 17458.2 | 10599.8 | -0.2000000000007276
2024-01-06 00:00:00 | 73128 |  |  | 1.72 |  | 2730.6 | 16449.3 | 16469.3 | -20 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024-03-10 00:00:00 | Blend | 73133 | 2243.69 | 1.62 | 3859.1468 | 35561.08
2024-01-07 00:00:00 | 73129 |  |  | 1.72 |  | 2619.82 | 13829.48 | 13819.48 | 10
2024-01-08 00:00:00 | FHIL00001296 | 16600 | 1.677 |  | 16619.5 |  | 30448.98
2024-01-08 00:00:00 | 73132 |  |  | 1.72 |  | 3254.01 | 27194.97 | 27194.97 | 0
2024-03-10 00:00:00 | FHIL00001285 | 10600 | 1.41 |  | 10599.8 |  | 37794.77 |  |  |  |  | Sales
2024-03-10 00:00:00 | 73133 |  |  |  |  | 2243.69 | 35551.08 | 35561.08 | -10 |  |  | Date | Reference | Ordered | Unit Price | Litres | Actual Balance