Automatically allocate the final paid date for the partially paid invoices

A

arrud14

New Member
Joined
Dec 17, 2022
Messages
14
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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
 

Attachments

  • IMG_20221217_234519.jpg
    IMG_20221217_234519.jpg
    229.7 KB · Views: 23

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
PLEASE use XL2BB when posting data!
Book1
ABCDEFGHI
1NumberNameDateAmtPaid DtNameAmtDate
21Rajbeer10/12/20223000PendingRajbeer150012/11/2022
32Suraj11/12/2022500012/15/2022Suraj300012/12/2022
43Nancy12/12/2022200012/14/2022rajbeer100012/14/2022
5nancy100012/13/2022
6nancy100012/14/2022
7Suraj200012/15/2022
Sheet1
Cell Formulas
RangeFormula
E2:E4E2=IF(SUMIFS($H$2:$H$7,$G$2:$G$7,B2)=D2,MAXIFS($I$2:$I$7,$G$2:$G$7,B2),"Pending")
 
Upvote 0
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!
Book1
ABCDEFGHI
1NumberNameDateAmtPaid DtNameAmtDate
21Rajbeer10/12/20223000PendingRajbeer150012/11/2022
32Suraj11/12/2022500012/15/2022Suraj300012/12/2022
43Nancy12/12/2022200012/14/2022rajbeer100012/14/2022
5nancy100012/13/2022
6nancy100012/14/2022
7Suraj200012/15/2022
Sheet1
Cell Formulas
RangeFormula
E2: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
Click to expand...
Cell Formulas
RangeFormula
 
Upvote 0
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



Cell Formulas
RangeFormula
Click to expand...
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?
 
Upvote 0
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
Click to expand...
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.
 
Upvote 0
Hello sir,
I have attached the same below.
Rich (BB code): 
ICODE]


NumberNameDateAmtPaidDtNameAmtDate
1Rajbeer10/12/2022300012/12/2022Rajbeer150012/11/2022
2Rajbeer11/12/2022500012/15/2022Rajbeer300012/12/2022
3Rajbeer12/12/2022200012/15/2022Rajbeer100012/14/2022
4Rajbeer13/12/2022400012/15/2022Rajbeer100012/13/2022
5Rajbeer14/12/2022700012/16/2022Rajbeer100012/14/2022
Rajbeer750012/15/2022
Rajbeer600012/16/2022
jdellasala said:
PLEASE use XL2BB when posting data!
Book1
ABCDEFGHI
1NumberNameDateAmtPaid DtNameAmtDate
21Rajbeer10/12/20223000PendingRajbeer150012/11/2022
32Suraj11/12/2022500012/15/2022Suraj300012/12/2022
43Nancy12/12/2022200012/14/2022rajbeer100012/14/2022
5nancy100012/13/2022
6nancy100012/14/2022
7Suraj200012/15/2022
Sheet1
Cell Formulas
RangeFormula
E2: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
Click to expand...
Cell Formulas
RangeFormula

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.
Click to expand...
 
Upvote 0
arrud14 said:
Hello sir,
I have attached the same below.
Click to expand...
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.
 
Upvote 0
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.
Click to expand...
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
 
Upvote 0
arrud14 said:
Sorry i am unable to use the minisheet in excel
Click to expand...
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
Click to expand...
Which is why we need some better representative sample data and expected results.

arrud14 said:
i have pasted the dummy data set
Click to expand...
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).
 
Upvote 0
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.


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).
Click to expand...
Plss help on the same

Book2.xlsx
ABCDEFGHIJKLM
1Invoice DetailsPayment Details
2LocationInvoice DateInvoice AmountInvoice No.NamesClearence dateOutstandingFosBankingDateBank
3AMBEDKARNAGAR05.12.2022259139453074RAJAN PAL06-Dec0CHANDAN330008-DecPUNB
4AMBEDKARNAGAR06.12.2022543239468693RAJAN PAL06-Dec0CHANDAN595011-DecPUNB
5AMBEDKARNAGAR06.12.2022480639460801RAJAN PAL10-Dec0CHANDAN600012-DecPUNB
6AMBEDKARNAGAR07.12.2022255039503179RAJAN PAL10-Dec0CHANDAN390013-DecPUNB
7AMBEDKARNAGAR08.12.2022330039542391CHANDAN08-Dec0CHANDAN800014-DecPUNB
8AMBEDKARNAGAR09.12.2022344439566311CHANDAN11-Dec0RAJAN PAL1210006-DecPUNB
9AMBEDKARNAGAR09.12.2022250539566584CHANDAN11-Dec0RAJAN PAL608210-DecPUNB
10AMBEDKARNAGAR09.12.2022275139574859RAJAN PAL10-Dec0RAJAN PAL1142112-DecPUNB
11AMBEDKARNAGAR12.12.2022341139608264CHANDAN12-Dec0RAJAN PAL371113-DecPUNB
12AMBEDKARNAGAR12.12.2022480539608111CHANDAN13-Dec0
13AMBEDKARNAGAR12.12.2022270039625463CHANDAN14-Dec0
14AMBEDKARNAGAR12.12.2022360039611283CHANDAN14-Dec0
15AMBEDKARNAGAR12.12.2022422539603022RAJAN PAL12-Dec0
16AMBEDKARNAGAR12.12.2022290239603295RAJAN PAL12-Dec0
17AMBEDKARNAGAR12.12.2022429439603153RAJAN PAL12-Dec0
18AMBEDKARNAGAR13.12.2022390039685842CHANDAN14-Dec515
19AMBEDKARNAGAR13.12.2022371139671011RAJAN PAL13-Dec-52
20
21
22
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Help with a formula
Replies
6
Views
120
paulsolar
P
I
  • Solved
Formula to lookup a date of payment
Replies
4
Views
138
InvoicingNoob
I
T
  • Question Question
A semi-advanced matching problem which may be solved with formulization or VBA
Replies
1
Views
353
The Godfather
T
B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
370
etaf
etaf
P
  • Question Question
Analytics Project - Sumifs
Replies
1
Views
559
bobaol
B

Forum statistics

Threads
1,224,814
Messages
6,181,124
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top