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)
)
)
)