Trying2learnVBA
Board Regular
- Joined
- Aug 21, 2019
- Messages
- 67
- Office Version
- 365
- 2021
- Platform
- Windows
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.
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 |