I have a input excel file which will be like this:
| ID |
AMOUNT |
DATE |
| 3119 |
41,202.04 |
11/24/23 |
| 6040 |
46,454.56 |
01/20/24 |
| 22125 |
45,470.04 |
01/29/24 |
| 22691 |
45,470.04 |
01/29/24 |
| 70039 |
37,400.32 |
02/08/24 |
I have one more master excel file, where it has data for all the months in a tab which is in the format like this:
| ID |
LAST_Date |
Sum Final Amount |
| 33020 |
5/31/2024 |
-16.85 |
| 3119 |
5/31/2024 |
16.53 |
| 6040 |
5/31/2024 |
512.43 |
| 22125 |
5/31/2024 |
-49.59 |
| 22691 |
5/31/2024 |
525.21 |
| 70039 |
5/31/2024 |
-33.88 |
| 77986 |
5/31/2024 |
101.07 |
| 78149 |
5/31/2024 |
151.61 |
My final table will be the input file.. along with the "Sum Final Amount" column. I need to check the minimum date from input excel file for the specific month, example here it is Novemeber 2023 So it should create new columns from minimum date's month till current date and also map values accordingly from the master excel file based on the tab name which is format (23-May) i.e YY-MMM. Pls find the output below:
| ID |
AMOUNT |
DATE |
Nov-23 |
23-Dec |
23-Jan |
23-Feb |
23-Mar |
23-Apr |
23-May |
| 3119 |
41,202.04 |
11/24/23 |
13 |
0 |
12 |
88 |
56 |
12 |
16.53 |
| 6040 |
46,454.56 |
01/20/24 |
0 |
0 |
45 |
45 |
463 |
11 |
512.43 |
| 22125 |
45,470.04 |
01/29/24 |
0 |
0 |
1000 |
233 |
232 |
96 |
-49.59 |
| 22691 |
45,470.04 |
01/29/24 |
0 |
0 |
200 |
244 |
56 |
100 |
525.21 |
| 70039 |
37,400.32 |
02/08/24 |
0 |
0 |
25 |
25 |
77 |
22 |
-33.88 |
