Ideas needed to automate a report

[Trying2learnVBA]

Hello,

I have a monthly report for loan advances - New and Maturing.
1.All new loans and maturities in the month have to be added to the report based on the funding date for New or Maturity date for Maturities.
2.Days without activity simply say no activity.

The issues I am facing - some days there are multiple transactions either two New advances or one of each or even more. In this case I need to insert rows with the same date.
The report has to include every single day in the month. I am attaching an example for this to make more sense.

But the main issue here is that a vlookup or index/match formula will not work because of new advances or maturities with the same date. The solution I need is to find a way to automatically add the dates in a month
from say 6/1/22 to 6/30/22 BUT keep in mind some dates occur more than once.

Example MiniSheet.xlsx
	A	B	C	D	E	F	G	H
1	Advance Number	Advance Amount	Trade Date	Funding Date	Maturity Date	Initial Interest Rate	Payment Frequency
2	36145017	100,000,000.00	6/2/2022 0:00	6/2/2022	11-Jul-22	0.0105	60	New
3	36145018	100,000,000.00	6/2/2022 0:00	6/2/2022	5-Aug-22	0.0118	90
4	36145019	100,000,000.00	6/2/2022 0:00	6/4/2022	1-Sep-22	0.0137	90
5	36145020	100,000,000.00	6/3/2022 0:00	6/8/2022	26-Aug-22	0.0131	90
6	36145021	100,000,000.00	6/3/2022 0:00	6/22/2022	27-Jul-22	0.0114	60
7	36145022	200,000,000.00	6/6/2022 0:00	6/24/2022	2-Sep-22	0.01344	90
8	36145023	50,000,000.00	24-Jun-22	6/24/2022	23-Jun-23	0.00271772	3/30/1900 0:00
9	36145024	250,000,000.00	6/29/2022 0:00	6/29/2022	13-Jul-22	0.0155	30
10	36145025	200,000,000.00	6/30/2022 0:00	6/30/2022	18-Jul-22	0.0158	30
11	36145026	200,000,000.00	6/30/2022 0:00	6/30/2022	25-Jul-22	0.0158	30
12	36145027	200,000,000.00	4/8/2022 0:00	6/3/2022	6/4/2022	0.0061	60	Mats
13	36145028	200,000,000.00	4/6/2022 0:00	6/6/2022	6/6/2022	0.0056	60
14	36145029	50,000,000.00	12/4/2020 0:00	6/8/2022	6/8/2022	0.01314	90
15	36145030	50,000,000.00	8/6/2021 0:00	6/8/2022	6/8/2022	0.0207	90
16
17
18
19	End result should be
20
21	Date	Advance#	Beg Bal	New Advance	Maturities
22	6/1/2022		Obtained from previous month
23	6/2/2022	36145017		100,000,000.00
24	6/2/2022	36145018		100,000,000.00
25	6/3/2022	NO ACTIVITY
26	6/4/2022	36145019	100,000,000.00
27	6/4/2022	36145027			200,000,000.00
28	6/5/2022	NO ACTIVITY
29	6/6/2022	36145028			200,000,000.00
30	6/7/2022	NO ACTIVITY
31	6/8/2022	36145020	100,000,000.00
32	6/8/2022	36145029			50,000,000.00
33	6/8/2022	36145030			50,000,000.00
34	6/11/2022
35	6/12/2022
36	6/13/2022
37	6/14/2022
38	6/15/2022
Sheet1

 

[SanjayGMusafir]

Update your Excel version here and in your profile. Solution shall depend on that.

 

[Trying2learnVBA]

Update your Excel version here and in your profile. Solution shall depend on that.

Sanjay,
I have version 2202 for Microsoft 365. It should be current.

I've been researching vba to insert rows based on criteria but I am not sure how to create a criteria to insert rows if the dates are not consecutive.

 

[Trying2learnVBA]

Update your Excel version here and in your profile. Solution shall depend on that.

BTW I made a mistake - the 100M amounts should not be under beginning balance. They should be in New Advance

 

[SanjayGMusafir]

Check this - I'm still not sure that you wanted this solution

Few things to mention -

• There can be some discrepancies as I was confused how you arrived at few figures in the sample result you wanted. Any such discrepancy can be worked upon provided you find solution is workable.
• I have changed date to numbers to keep uniformity - date are written differently in your and my part of the world. So you can convert to your format and check.
• I couldn't find a sustainable solution to incorporate dates where no transaction was done. So I used conditional formatting to identify that few dates are missing between the dates.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Advance Number	Advance Amount	Trade Date	Funding Date	Maturity Date	Initial Interest Rate	Payment Frequency
2	36145017	100000000	44,714	44,714	44,753	0.0105	60	New
3	36145018	100000000	44,714	44,714	44,778	0.0118	90
4	36145019	100000000	44,714	44,716	44,805	0.0137	90
5	36145020	100000000	44,715	44,720	44,799	0.0131	90
6	36145021	100000000	44,715	44,734	44,769	0.0114	60
7	36145022	200000000	44,718	44,736	44,806	0.01344	90
8	36145023	50000000	44,736	44,736	45,100	0.00271772	90
9	36145024	250000000	44,741	44,741	44,755	0.0155	30
10	36145025	200000000	44,742	44,742	44,760	0.0158	30
11	36145026	200000000	44,742	44,742	44,767	0.0158	30
12	36145027	200000000	44,659	44,715	44,716	0.0061	60	Mats
13	36145028	200000000	44,657	44,718	44,718	0.0056	60
14	36145029	50000000	44,169	44,720	44,720	0.01314	90
15	36145030	50000000	44,414	44,720	44,720	0.0207	90
16
17
18
19	End result should be						My Solution
20
21	Date	Advance#	Beg Bal	New Advance	Maturities		Date	Advance#	Beg Bal	New Advance	Maturities	Maturity Date
22	1/6/22		Obtained from previous month				44,713
23	2/6/22	36145017		100000000			44,714	36145017		100000000
24	2/6/22	36145018		100000000			44,714	36145018		100000000
25	3/6/22	NO ACTIVITY					44,715	36145027			200000000	44,716
26	4/6/22	36145019	100000000				44,716	36145019	100000000
27	4/6/22	36145027			200000000		44,718	36145028			200000000	44,718
28	5/6/22	NO ACTIVITY					44,720	36145020	100000000
29	6/6/22	36145028			200000000		44,720	36145029			50000000	44,720
30	7/6/22	NO ACTIVITY					44,720	36145030			50000000	44,720
31	8/6/22	36145020	100000000				44,734	36145021	100000000
32	8/6/22	36145029			50000000		44,736	36145022	200000000
33	8/6/22	36145030			50000000		44,736	36145023		50000000
34	11/6/22						44,741	36145024		250000000
35	12/6/22						44,742	36145025		200000000
36	6/13/2022						44,742	36145026		200000000
37	6/14/2022
38	6/15/2022
Sheet1

Cell Formulas
Range	Formula
G23:G36	G23	=SORT(FILTER(D2:D15,(D2:D15>=$G$22)*(D2:D15<=EOMONTH($G$22,0))),1,1)
H23:H36	H23	=SORTBY(FILTER(A2:A15,(D2:D15>=$G$22)*(D2:D15<=EOMONTH($G$22,0))),D2:D15,1)
I23:I36	I23	=XLOOKUP(1,($D$2:$D$15=G23)*($A$2:$A$15=H23)*($C$2:$C$15<G23)*($E$2:$E$15>EOMONTH($G$22,0)),$B$2:$B$15,"")
J23:J36	J23	=XLOOKUP(1,($D$2:$D$15=G23)*($A$2:$A$15=H23)*($C$2:$C$15=G23),$B$2:$B$15,"")
K23:K36	K23	=XLOOKUP(1,($A$2:$A$15=H23)*($E$2:$E$15<=EOMONTH($G$22,0)),$B$2:$B$15,"")
L23:L36	L23	=XLOOKUP(1,($A$2:$A$15=H23)*($E$2:$E$15<=EOMONTH($G$22,0)),$E$2:$E$15,"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G23:G36	Expression	=G23>G22+1	text	NO