Finding date of last payment in excel sheet

[dloskot]

I am on the board of charity that does micro loans in Africa and we used excel sheet to keep track of loan repayments. I would like to find the date of the last payment. Column A has the date and Column C has the payment. All the payment cells are pre filled with zeros. I think a CSE formula could be used but I have not been able to find the right formula to do this. I am hoping some here can help me write the correct formula. Below is an example of that a repayment sheet would look like. In this example I would like to have a formula that would return the date of 23-Dec-14 as that was the date of the last payment as shown in Column C.

A B C D E F G H
1 Payment Date Weekly Amt Payment Past Due Paid to Date Loan Balance Savings Saving Bal
2 2-Dec-14 19,300 38,600 0 38,600 461,400 0 0
3 9-Dec-14 19,300 10,000 9,300 48,600 451,400 0 0
4 16-Dec-14 19,300 0 28,600 48,600 451,400 0 0
5 23-Dec-14 19,300 15,000 47,900 48,600 451,400 0 0
6 30-Dec-14 19,300 0 67,200 48,600 451,400 0 0

I hope someone can help me with this.

Doug

 

[Aladin Akyurek]

Sure I can provide sample data, but how do I do that? My posting permissions say I can't not post attachments?

No need for an attachment...

I have no clue what you are doing...

If you want to run the formula for each sheet, you need to do so separately for each sheet... Defined range names do not hold for 100 sheets. Just for one sheet.

It's unclear what you are up to. Are you trying to get the last non-zero payment date from each sheet?

Care to comment about the quoted bit above?

 

[JoeMo]

Sure I can provide sample data, but how do I do that? My posting permissions say I can't not post attachments?

Here are two links to free html applications. Either one will allow you to post portions of your speadsheet. For example, I used the first one in my post#7 of this thread.

Excel Jeanie link: Download
MrExcel HTML Maker link: http://www.mrexcel.com/forum/2545970-post2.html

 

[dloskot]

Sorry to be so unclear. I have a wookbook. It has 10 to 15 sheet (tabs at the bottom) each sheet is the loan repayment for one person. I have inserted the formula on each of the separate sheets in workbook but using JoeMo's formula I get the correct answer on some sheets but not the others. As stated before when I try to enter you formula I can an error about circular reference.

 

[dloskot]

Here is a like to the file in dropbox. I hope it works. Starting with the second sheet I have entered your formula. The first sheet comes up with the correct date but the next does not. If you look at each one some are correct and others are not.
Hope this link works for you.

https://www.dropbox.com/s/eou6n0hxwhmhz6x/Uganda%20Nakibizzi%20Loan%20Repayment.xlsx?dl=0

 

[Aladin Akyurek]

Sorry to be so unclear. I have a wookbook. It has 10 to 15 sheet (tabs at the bottom) each sheet is the loan repayment for one person. I have inserted the formula on each of the separate sheets in workbook but using JoeMo's formula I get the correct answer on some sheets but not the others. As stated before when I try to enter you formula I can an error about circular reference.

The formulas are equivalent. The one I suggested is dynamic.

1. In each sheet, column A houses true dates. Right?

2. In each sheet, column C houses payments. Right?

3. You want the payment date corresponding to the last non-zero (>0) payment. Right?

4. The formula for each sheet must be entered outside the area of the data, say in column X.

Sheet1

In X1 enter:

=MATCH(9.99999999999999E+307,A:A)

In X2 enter:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/C2:INDEX(C:C,X1)),A2:INDEX(A:A,X1))

Do the same exactly in every other sheet in the same locations.

If so desired, you can collect the result dates in a summary sheet...

summary

A1: Sheet1

B1:

=INDIRECT("'"&A1&"'!X2")

A2: Sheet2

Copy down B1 to B2.

And so on.

Good luck.

 

[JoeMo]

Here is a like to the file in dropbox. I hope it works. Starting with the second sheet I have entered your formula. The first sheet comes up with the correct date but the next does not. If you look at each one some are correct and others are not.
Hope this link works for you.

https://www.dropbox.com/s/eou6n0hxwhmhz6x/Uganda Nakibizzi Loan Repayment.xlsx?dl=0

Sorry, I don't download from the internet.

Are you certain that all "dates" on the sheets where the formula is not working are actual dates (numbers) as opposed to text?

 

[dloskot]

Joe: Yes they are all date, however some are calculated i.e. I take the first cell add 7 to it and so on done the column. However in one of the sheet that was not working I replaced them with the actual dates and it didn't change anything.
I will try the excel Jennie link a little later so you can see the actual data. Thanks for all the help and hanging in there with me.

 

[dloskot]

Excel Jennie doesn't work with Excel 2010 and Norton said there is a problem with the setup.exe. I tried to paste in a picture from dropbox url but it didn't work. See if you can make sense of the below.

I typed the below data into a clean sheet and used the formula =INDEX(A4:C66,MATCH(LOOKUP(2,1/(C4:C56<>0),C4:C66),C4:C56,0),1) and I used CSE to enter it. The data started in row 4 of the sheet . Date in A B was Blank and C had the payments. A4 had 11-26-2104 and A5 through A56 were the cell above it +7 to get the rest of the dates. You can see that at the bottom it comes up with 26-Nov-14 as the last payment which is of course wrong.

26-Nov-14 7600
3-Dec-14 7600
10-Dec-14 1520
17-Dec-14 0
24-Dec-14 0
31-Dec-14 0
7-Jan-15 20200
14-Jan-15 10000
21-Jan-15 7600
28-Jan-15 7600
4-Feb-15 7600
11-Feb-15 7600
18-Feb-15 7600
25-Feb-15 7600
4-Mar-15 0
11-Mar-15 0
18-Mar-15 0
25-Mar-15 7600
1-Apr-15 7600
8-Apr-15 7600
15-Apr-15 7600
22-Apr-15 0
29-Apr-15 4000
6-May-15 7600
13-May-15 7600
20-May-15 7600
27-May-15 0
3-Jun-15 0
10-Jun-15 7600
17-Jun-15 7600
24-Jun-15 0
1-Jul-15 0
8-Jul-15 0
15-Jul-15 0
22-Jul-15 0
29-Jul-15 0
5-Aug-15 0
12-Aug-15 0
19-Aug-15 0
26-Aug-15 0
2-Sep-15 0
9-Sep-15 0
16-Sep-15 0
23-Sep-15 0
30-Sep-15 0
7-Oct-15 0
14-Oct-15 0
21-Oct-15 0
28-Oct-15 0
4-Nov-15 0
11-Nov-15 0
18-Nov-15 0
25-Nov-15 0

26-Nov-14

 

[dloskot]

Aladin: BINGO!!!!! That works. I don't understand what is different but I just tried it on 10 sheets that were not working before and now they all calculate the correct date. Thank you so much. I know you spent more time on it that I am sure you wanted but it works and that is great. I and the Charity greatly appreciate it.
If you have time and are willing could you explain how it works. It looks like the first formula determines the last row is row 56, but I don't understand how the second formula finds the last payment?

JoeMo. Also thank you for working on this not sure why I was having problems with your formula but at least now I have a solution.

 

[JoeMo]

Excel Jennie doesn't work with Excel 2010 and Norton said there is a problem with the setup.exe. I tried to paste in a picture from dropbox url but it didn't work. See if you can make sense of the below.

I typed the below data into a clean sheet and used the formula =INDEX(A4:C66,MATCH(LOOKUP(2,1/(C4:C56<>0),C4:C66),C4:C56,0),1) and I used CSE to enter it. The data started in row 4 of the sheet . Date in A B was Blank and C had the payments. A4 had 11-26-2104 and A5 through A56 were the cell above it +7 to get the rest of the dates. You can see that at the bottom it comes up with 26-Nov-14 as the last payment which is of course wrong.

Before I look at your data, let me just note that the formula you posted here in the above quote is wrong. The ranges noted in red font must all be the same. If that's not a typo, try again with the correct formula.