# Automatically allocate the final paid date for the partially paid invoices



## arrud14 (Dec 17, 2022)

Hii 
I have a data set containing the invoice details and the details of recurring payment received from the customer and i want to allocate the final paid date for the every particular invoice calculating the sum of amount automatically


----------



## jdellasala (Dec 18, 2022)

PLEASE use *XL2BB* when posting data!
Book1ABCDEFGHI1NumberNameDateAmtPaid DtNameAmtDate21Rajbeer10/12/20223000PendingRajbeer150012/11/202232Suraj11/12/2022500012/15/2022Suraj300012/12/202243Nancy12/12/2022200012/14/2022rajbeer100012/14/20225nancy100012/13/20226nancy100012/14/20227Suraj200012/15/2022Sheet1Cell FormulasRangeFormulaE2:E4E2=IF(SUMIFS($H$2:$H$7,$G$2:$G$7,B2)=D2,MAXIFS($I$2:$I$7,$G$2:$G$7,B2),"Pending")


----------



## arrud14 (Dec 19, 2022)

Hello sir,
I appreciate your help but could you please let me know how can we do the same if there are multiple invoices and payments of the same customer 





jdellasala said:


> PLEASE use *XL2BB* when posting data!
> Book1ABCDEFGHI1NumberNameDateAmtPaid DtNameAmtDate21Rajbeer10/12/20223000PendingRajbeer150012/11/202232Suraj11/12/2022500012/15/2022Suraj300012/12/202243Nancy12/12/2022200012/14/2022rajbeer100012/14/20225nancy100012/13/20226nancy100012/14/20227Suraj200012/15/2022Sheet1Cell FormulasRangeFormulaE2:E4E2=IF(SUMIFS($H$2:$H$7,$G$2:$G$7,B2)=D2,MAXIFS($I$2:$I$7,$G$2:$G$7,B2),"Pending")[/RANGE


Cell FormulasRangeFormula


----------



## jdellasala (Dec 19, 2022)

arrud14 said:


> Hello sir,
> I appreciate your help but could you please let me know how can we do the same if there are multiple invoices and payments of the same customer
> 
> 
> ...


I thought it does. Columns G-I are the payments showing multiple payments. The formula sums all of the payments (H) for the name in Column B and if it equals the amount in Column D returns the latest payment date (H), otherwise it returns Pending.
Isn't that what you wanted?


----------



## Peter_SSs (Dec 19, 2022)

arrud14 said:


> could you please let me know how can we do the same if there are multiple invoices and payments of the same customer


Could you give us some dummy, but representative, sample data where that situation exists with say 2 or 3 invoices per customer and multiple payments and manually fill in the desired results.
Please post that with XL2BB so that we can easily copy to our worksheets to test.


----------



## arrud14 (Dec 19, 2022)

Hello sir,
I have attached the same below.

```
ICODE]
NumberNameDateAmtPaidDtNameAmtDate1Rajbeer10/12/2022300012/12/2022Rajbeer150012/11/20222Rajbeer11/12/2022500012/15/2022Rajbeer300012/12/20223Rajbeer12/12/2022200012/15/2022Rajbeer100012/14/20224Rajbeer13/12/2022400012/15/2022Rajbeer100012/13/20225Rajbeer14/12/2022700012/16/2022Rajbeer100012/14/2022Rajbeer750012/15/2022Rajbeer600012/16/2022
```



jdellasala said:


> PLEASE use *XL2BB* when posting data!
> Book1ABCDEFGHI1NumberNameDateAmtPaid DtNameAmtDate21Rajbeer10/12/20223000PendingRajbeer150012/11/202232Suraj11/12/2022500012/15/2022Suraj300012/12/202243Nancy12/12/2022200012/14/2022rajbeer100012/14/20225nancy100012/13/20226nancy100012/14/20227Suraj200012/15/2022Sheet1Cell FormulasRangeFormulaE2:E4E2=IF(SUMIFS($H$2:$H$7,$G$2:$G$7,B2)=D2,MAXIFS($I$2:$I$7,$G$2:$G$7,B2),"Pending")[/RANGE


Cell FormulasRangeFormula


Peter_SSs said:


> Could you give us some dummy, but representative, sample data where that situation exists with say 2 or 3 invoices per customer and multiple payments and manually fill in the desired results.
> Please post that with XL2BB so that we can easily copy to our worksheets to test.


----------



## Peter_SSs (Dec 20, 2022)

arrud14 said:


> Hello sir,
> I have attached the same below.


Not as easy to copy or to see what ranges the values are in as with XL2BB. Is there a problem using that?

Also, with only one name in column B and only the same name in column H, I doubt that is representative of your real data. If the names *are *all the same like that please advise, otherwise some more representative (varied) data would be better.


----------



## arrud14 (Dec 20, 2022)

Peter_SSs said:


> Not as easy to copy or to see what ranges the values are in as with XL2BB. Is there a problem using that?
> 
> Also, with only one name in column B and only the same name in column H, I doubt that is representative of your real data. If the names *are *all the same like that please advise, otherwise some more representative (varied) data would be better.


Sorry i am unable to use the minisheet in excel 

But from column A to E it contains the invoice details and. From column H it contains all the recurring payments made by the customer on different dates and i want to use the logic or formula here which could automatically allocate the payment date on which the invoice is being cleared calculating the sum of amount

Also the rajbeer is not my only customer i have different customer invoices with different names i have pasted the dummy data set just to understand how i can automate this task


----------



## Peter_SSs (Dec 20, 2022)

arrud14 said:


> Sorry i am unable to use the minisheet in excel


Is that a workplace restriction or did you just run into a problem somewhere?



arrud14 said:


> Also the rajbeer is not my only customer i have different customer invoices with different names


Which is why we need some better representative sample data and expected results.



arrud14 said:


> i have pasted the dummy data set


But the last one it had only one customer in each table and the first one did not have examples of multiple invoices for a customer.

If you cannot use XL2BB then just copy/paste some smallish but representative sample data directly into your post (with no tags).


----------



## arrud14 (Wednesday at 1:32 PM)

Peter_SSs said:


> Is that a workplace restriction or did you just run into a problem somewhere?
> 
> 
> Which is why we need some better representative sample data and expected results.
> ...


Plss help on the same

Book2.xlsxABCDEFGHIJKLM1Invoice DetailsPayment Details2LocationInvoice DateInvoice AmountInvoice No.NamesClearence dateOutstandingFosBankingDateBank3AMBEDKARNAGAR05.12.2022259139453074RAJAN PAL06-Dec0CHANDAN330008-DecPUNB4AMBEDKARNAGAR06.12.2022543239468693RAJAN PAL06-Dec0CHANDAN595011-DecPUNB5AMBEDKARNAGAR06.12.2022480639460801RAJAN PAL10-Dec0CHANDAN600012-DecPUNB6AMBEDKARNAGAR07.12.2022255039503179RAJAN PAL10-Dec0CHANDAN390013-DecPUNB7AMBEDKARNAGAR08.12.2022330039542391CHANDAN08-Dec0CHANDAN800014-DecPUNB8AMBEDKARNAGAR09.12.2022344439566311CHANDAN11-Dec0RAJAN PAL1210006-DecPUNB9AMBEDKARNAGAR09.12.2022250539566584CHANDAN11-Dec0RAJAN PAL608210-DecPUNB10AMBEDKARNAGAR09.12.2022275139574859RAJAN PAL10-Dec0RAJAN PAL1142112-DecPUNB11AMBEDKARNAGAR12.12.2022341139608264CHANDAN12-Dec0RAJAN PAL371113-DecPUNB12AMBEDKARNAGAR12.12.2022480539608111CHANDAN13-Dec013AMBEDKARNAGAR12.12.2022270039625463CHANDAN14-Dec014AMBEDKARNAGAR12.12.2022360039611283CHANDAN14-Dec015AMBEDKARNAGAR12.12.2022422539603022RAJAN PAL12-Dec016AMBEDKARNAGAR12.12.2022290239603295RAJAN PAL12-Dec017AMBEDKARNAGAR12.12.2022429439603153RAJAN PAL12-Dec018AMBEDKARNAGAR13.12.2022390039685842CHANDAN14-Dec51519AMBEDKARNAGAR13.12.2022371139671011RAJAN PAL13-Dec-52202122Sheet1


----------



## arrud14 (Dec 17, 2022)

Hii 
I have a data set containing the invoice details and the details of recurring payment received from the customer and i want to allocate the final paid date for the every particular invoice calculating the sum of amount automatically


----------



## Peter_SSs (Wednesday at 9:07 PM)

It is not clear to me. Your dates in column F all appear to be dates in 2023. Your dates in column K all appear to be dates in 2022.
For example, cell F3 shows this



45266 is the value for the date 6 December 2023.

So how did you get that clearance date for that cell?


----------



## arrud14 (Wednesday at 11:11 PM)

Peter_SSs said:


> It is not clear to me. Your dates in column F all appear to be dates in 2023. Your dates in column K all appear to be dates in 2022.
> For example, cell F3 shows this
> View attachment 82591
> 45266 is the value for the date 6 December 2023.
> ...


That was an mistake as i have manually put the dates in the column F but i want to allocate the payments dates in the COULMN F calculating the sum of invoices and payments 
 As shown in the table from 5th Dec 2022 to 6th Dec 2022 there are three invoices for rajan pal (2591,5432,4806) the total of which is 12829 and then on 6th Dec 2022 Rajan Pal Paid 12100 so the amount of rs 729 was outstanding from the invoice no. 3rd ( 4806 ) which gets cleared on next payment which is 10th Dec


----------



## Peter_SSs (Yesterday at 12:22 AM)

arrud14 said:


> That was an mistake


OK, I assume all those column F dates were meant to be 2022.

See if this works for you.

arrud14.xlsmBCDEFHIJKL2Invoice DateInvoice AmountInvoice No.NamesClearence dateFosBankingDateBank305.12.2022259139453074RAJAN PAL06-Dec-22CHANDAN330008-Dec-22PUNB406.12.2022543239468693RAJAN PAL06-Dec-22CHANDAN595011-Dec-22PUNB506.12.2022480639460801RAJAN PAL10-Dec-22CHANDAN600012-Dec-22PUNB607.12.2022255039503179RAJAN PAL10-Dec-22CHANDAN390013-Dec-22PUNB708.12.2022330039542391CHANDAN08-Dec-22CHANDAN800014-Dec-22PUNB809.12.2022344439566311CHANDAN11-Dec-22RAJAN PAL1210006-Dec-22PUNB909.12.2022250539566584CHANDAN11-Dec-22RAJAN PAL608210-Dec-22PUNB1009.12.2022275139574859RAJAN PAL10-Dec-22RAJAN PAL1142112-Dec-22PUNB1112.12.2022341139608264CHANDAN12-Dec-22RAJAN PAL371113-Dec-22PUNB1212.12.2022480539608111CHANDAN13-Dec-221312.12.2022270039625463CHANDAN14-Dec-221412.12.2022360039611283CHANDAN14-Dec-221512.12.2022422539603022RAJAN PAL12-Dec-221612.12.2022290239603295RAJAN PAL12-Dec-221712.12.2022429439603153RAJAN PAL12-Dec-221813.12.2022390039685842CHANDANnot paid1913.12.2022371139671011RAJAN PAL13-Dec-22Sheet1Cell FormulasRangeFormulaF3:F19F3=LET(n,FILTER(J$3:K$11,I$3:I$11=E3,""),XLOOKUP(SUMIF(E$3:E3,E3,C$3:C3),INDEX(SCAN(0,INDEX(n,0,1),LAMBDA(t,s,t+s)),0,1),INDEX(n,0,2),"not paid",1))


----------



## arrud14 (Yesterday at 10:49 AM)

I am using excel 2019 and it doesnt accepting/ supporting FILTER formula


----------



## arrud14 (Yesterday at 10:50 AM)

Peter_SSs said:


> OK, I assume all those column F dates were meant to be 2022.
> 
> See if this works for you.
> 
> arrud14.xlsmBCDEFHIJKL2Invoice DateInvoice AmountInvoice No.NamesClearence dateFosBankingDateBank305.12.2022259139453074RAJAN PAL06-Dec-22CHANDAN330008-Dec-22PUNB406.12.2022543239468693RAJAN PAL06-Dec-22CHANDAN595011-Dec-22PUNB506.12.2022480639460801RAJAN PAL10-Dec-22CHANDAN600012-Dec-22PUNB607.12.2022255039503179RAJAN PAL10-Dec-22CHANDAN390013-Dec-22PUNB708.12.2022330039542391CHANDAN08-Dec-22CHANDAN800014-Dec-22PUNB809.12.2022344439566311CHANDAN11-Dec-22RAJAN PAL1210006-Dec-22PUNB909.12.2022250539566584CHANDAN11-Dec-22RAJAN PAL608210-Dec-22PUNB1009.12.2022275139574859RAJAN PAL10-Dec-22RAJAN PAL1142112-Dec-22PUNB1112.12.2022341139608264CHANDAN12-Dec-22RAJAN PAL371113-Dec-22PUNB1212.12.2022480539608111CHANDAN13-Dec-221312.12.2022270039625463CHANDAN14-Dec-221412.12.2022360039611283CHANDAN14-Dec-221512.12.2022422539603022RAJAN PAL12-Dec-221612.12.2022290239603295RAJAN PAL12-Dec-221712.12.2022429439603153RAJAN PAL12-Dec-221813.12.2022390039685842CHANDANnot paid1913.12.2022371139671011RAJAN PAL13-Dec-22Sheet1Cell FormulasRangeFormulaF3:F19F3=LET(n,FILTER(J$3:K$11,I$3:I$11=E3,""),XLOOKUP(SUMIF(E$3:E3,E3,C$3:C3),INDEX(SCAN(0,INDEX(n,0,1),LAMBDA(t,s,t+s)),0,1),INDEX(n,0,2),"not paid",1))


I am using excel 2019 and it doesnt accepting/ supporting FILTER formula


----------



## Peter_SSs (Yesterday at 2:44 PM)

arrud14 said:


> I am using excel 2019


😦 Developing a suggestion was based on this:


----------



## arrud14 (Yesterday at 11:31 PM)

Peter_SSs said:


> 😦 Developing a suggestion was based on this:
> 
> View attachment 82640


Could you pls let me know how can i do rhe same with excel 2019


----------



## Peter_SSs (Yesterday at 11:48 PM)

arrud14 said:


> Could you pls let me know how can i do rhe same with excel 2019


While I am thinking about that, could you please fix your profile details? (so future helpers do not waste their times developing a solution that does not work for you)


----------



## arrud14 (Today at 12:42 PM)

Peter_SSs said:


> While I am thinking about that, could you please fix your profile details? (so future helpers do not waste their times developing a solution that does not work for you)


Sir i really apologize for your inconvenience but i use both versions of excel 2019 in my official laptop and excel 2021 in my personal laptop that why i just want you to let me know how i can do the same with excel 2019  if possible


----------

