Code to return date multiple criteria

Hi all,

I have no knowledge of VBA whatsoever and there is a function I need that is impossible to get on excel 2016.

attached you can find my data set with 4 columns A to D. the order of the data in these columns will change except from the headers. I would really like to have a code that will generate columns F and G.

column F is a consolidation of all unique numbers (loan ids) from column A (might include text and numbers i.e 1025A).

column G returns a certain date. For each loan id the date would be the last "to be paid" date which represents the cumulative amount of payments that have not been covered untill this date.

For example, loan number 1006 paid at 9 different dates and reached a total of 89,855. By the date 20/09/2021 this loan should have paid 90,000 and this amount has never been covered. the result I need is the date 20/09/2021.

Another example, loan number 1006 paid at 11 different dates and reached a total of 121,000. by the date 10/03/2022 this loan should have paid 141,420 this amount has never been covered. the result I need is the date 10/03/2022.

Hello. I must respectfully disagree with your analyses. I mean:

- 1004. The date should be July 20, 2021.

- 1006. The date should be October 10, 2021.
This last case seems to indicate that your analyzes are not by rigorous date but by "month and year". If so, then the date for 1006 would indeed be March 10, 2022.

Comments?..

Hi,

1004 - why do you think the date should be July 20, 2021? the date that i need is for sure Sep 20, 2021.

1004 paid at 9 different dates and reached a total of 89,855. By the date 20/09/2021 this loan should have paid 90,000 and this amount has never been covered. thus, i need to return the date 20/09/2021.

Originally Posted by riiiiiicjjj

1004 - why do you think the date should be July 20, 2021? the date that i need is for sure Sep 20, 2021.

Because as of July 20, 2001 you have:

paid = 51500
to be paid = 60000

thats right, but its not what i need.

cumulative amount of payments made for 1004 = 89,855. last date of "to be paid" when a cumulative amount covered- 20/08/2021 75,000. last date not covered - 20/09/2021 90,000.

thanks, unfortunately this does not give me the final data i need. i need to generate these dates

Click on the button and analyze:

thank you for your time. result is not correct, when macro runs than 1004 returns 20/07/2021. should return 20/09/2021.
the cumulative amount of payments made for 1004- 89,855. the last cumulative amount that has not covered is 90,000. the date in which 90,000 should have paid is 20/09/2021. this is the date i need

With the macro that I have attached in my last post I demonstrate that:

- For 1006, the first date on which the balance becomes negative is taken.

- But for 1004 the second date in which the balance becomes negative is taken.

Put yourself in my place and note that your criteria is not uniform: do you understand me?...

i understand, the criteria should be:
- take the cumulative amount of payments made
- return the date of payment to be made which represents the most recent cumulative amount that is not covered - i.e. 90,000
that means that the criteria is not the first negative balance

Ahhh!... Then we change the order in which we have the data and that's it:

Look at the following:

many thanks.
in the attached file you can see that only 3 unique loan numbers are consolidated, could please tell me why? there are 9 unique in the data set. possible to include all in the code?

hi Glenn, i have attached an example that i think describes properly my needs. beyond excel really helps

Originally Posted by riiiiiicjjj

beyond excel really helps

Did you look at post #13?...

indeed, seems to work well. thank you so much for you time