Forum Discussion
Map invoice and receipt on FIFO basis
Hi, i am attaching a sample data below, in which i have amounts in Positive as Invoices raised and Negative Values as Amount received, now i want to map receipts against invoice documents on FIFO bas...
Hi, I have tried to understand your task and have developed a formula that works under Office 365. But I'm not sure if it fits.
| Document No | Document Date | Document Totals | Adjusted Amount | Adjusted Document No | Adjusted Date |
| 1 | 11/26/22 | 737429 | |||
| 2 | 11/30/22 | 809893 | |||
| 3 | 01/27/23 | 281682 | |||
| 4 | 01/31/23 | 348288 | |||
| 5 | 03/31/23 | 599277 | |||
| -400000 | 6 | 04/03/23 | |||
| 7 | 04/05/23 | 200000 | |||
| -162594 | 8 | 04/06/23 | |||
| -400000 | 9 | 04/17/23 | |||
| -9299.81 | 10 | 04/21/23 | |||
| -100000 | 11 | 04/24/23 | |||
| -100000 | 12 | 04/26/23 | |||
| -100000 | 13 | 05/04/23 |
=LET(
data,SORT(A2:C14,2),
totals,C2:C14,
VSTACK(
HSTACK(A1:C1,"Adjusted Amount","Adjusted Document No","Adjusted Date"),
HSTACK(IF(totals>0,data,""),CHOOSECOLS(IF(totals<0,data,""),3,1,2)
)
)
)
Hi dscheikey
Thanks you so much for your efforts, but as per output in your sheet, positives and negatives columns have been separated, but my desired output is to map negative amounts against positive amounts on FIFO basis, each negative line item needs to be mapped against a positive line to the extent of positive amount on FIFO basis in the way below
Regards
Sandeep Daga