Convert formulas into a code & get the extract

[RAJESH1960]

Hello guys,
I have created this new app with the help of formulas. I need your expertise to get the result in the extract sheet as shown in the workbook by converting the formulas into code. The details are in the workbook Data sheet. Thank you in advance.
The date in column B is actually the starting Date to be posted first in the extract sheet.
Loading Google Sheets

 

[johnnyL]

Get extract in new sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	DAY	DATE	PARTICULARS	MIN	MAX	Multiples	No of Rows			ROW	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	Sun	4/1/2022	January	60	100	10	12	First Date to start in Extract sheet as in Data sheet cell B2			90.00	1200.00	120.00	140.00	170.00	10000.00	10000.00	12000.00	260.00	15000.00	90.00	55.00	25.00	10.00
3	Mon	4/2/2022	February	1200	1200	10	52	First Date to start in Extract sheet as in Data sheet cell B3
4	Fri	4/6/2022	March	100	200	10	52	First Date to start in Extract sheet as in Data sheet cell B4
5	Sun	4/1/2022	April	120	150	10	365	First Date to start in Extract sheet as in Data sheet cell B5
6	Wed	4/25/2022	May	150	200	10	12	First Date to start in Extract sheet as in Data sheet cell B6
7	Tue	4/3/2022	June	10000	10000	1	12	First Date to start in Extract sheet as in Data sheet cell B7
8	Thu	4/5/2022	July	10000	10000	1	24	First Date to start in Extract sheet as in Data sheet cell B8 & next date 15 days after the date in cell B8
9	Sat	4/7/2022	August	12000	12000	1	24	First Date to start in Extract sheet as in Data sheet cell B9 & next date 15 days after the date in cell B9
10	Wed	4/4/2022	September	200	500	10	52	First Date to start in Extract sheet as in Data sheet cell B10
11	Wed	3/25/2023	October	15000	15000	1	1	First Date to start in Extract sheet as in Data sheet cell B11
12	Sun	4/1/2022	November	10	100	10	120	First Date to start in Extract sheet as in Data sheet cell B12
13	Tue	4/3/2022	December	10	100	5	7	First Date to start in Extract sheet as in Data sheet cell B13
14	Mon	4/2/2022	Sunday	15	50	5	7	First Date to start in Extract sheet as in Data sheet cell B14
15	Sun	4/1/2022	Monday	8	12	1	365	First Date to start in Extract sheet as in Data sheet cell B15
16
17
18				Code to do the following:
19				Get formula in the code from columns K2 to X2
20				Columns K to X to resize rows K2 to X2 and fill down as per the number of rows mentioned in column G
21			1	Write Headings in the Extract sheet as shown
22			2	Copy Date from Data sheet row B2 to Extract sheet cell B2 and as per G2 drag the dates to the number of rows mentioned in column G
23				In case the no of rows is 12and If the date is 01-04-2022 then get 01-05-2022, 01-06-2022 and so on.
24				In case the no of rows is 52 and If the date is 01-04-2022 then get 01-05-2022+7 and fill down till 52 rows
25				In each case the number of rows mentioned in column G, that many rows to be filled in the extract sheet starting from the date mentioned in column B of the Data sheet.
26			3	In the amount column of the Extract sheet, take the values of that particular row from K to X.
27			4	Copy date from Data sheet row B3 to Extract sheet to the next empty cell and follow the same procedure as mentioned in 2
28				Follow the procedure till data is available in column C of the data sheet.
29			5	After all the data is entered in Extract sheet, fill the line column with numbers in series from 1 to end.
30				The extract sheet result is manually entered for 2 rows of data only to show the expected result.
31
Data

Cell Formulas
Range	Formula
K2:X2	K2	=IFERROR(MROUND(RANDBETWEEN(INDEX($D$1:$D$15,K$1),INDEX($E$1:$E$15,K$1)),INDEX($F$1:$F$15,K$1)),"")
A2:A15	A2	=DAY(B2)

In case the no of rows is 12and If the date is 01-04-2022 then get 01-05-2022, 01-06-2022 and so on.

In case the no of rows is 52 and If the date is 01-04-2022 then get 01-05-2022+7 and fill down till 52 rows

You haven't stated how the other G column values should be handled ... 365, 24, 1, 7, 120

 

[johnnyL]

Get extract in new sheet.xlsx
	A	B	C	D	E
1	Line	DATE	PARTICULARS	AMOUNT
2	1	4/30/2022	January	80.00
3	2	5/30/2022	January	80.00
4	3	6/30/2022	January	90.00
5	4	7/30/2022	January	70.00
6	5	8/30/2022	January	60.00
7	6	9/30/2022	January	90.00
8	7	10/30/2022	January	70.00
9	8	11/30/2022	January	80.00
10	9	12/30/2022	January	70.00
11	10	1/30/2023	January	60.00
12	11	2/28/2023	January	70.00
13	12	3/30/2023	January	70.00
14	13	4/2/2022	February	1200.00
15	14	4/9/2022	February	1200.00
16	15	4/16/2022	February	1200.00
17	16	4/23/2022	February	1200.00
18	17	4/30/2022	February	1200.00
19	18	5/7/2022	February	1200.00
20	19	5/14/2022	February	1200.00
21	20	5/21/2022	February	1200.00
22	21	5/28/2022	February	1200.00
23	22	6/4/2022	February	1200.00
24	23	6/11/2022	February	1200.00
25	24	6/18/2022	February	1200.00
26	25	6/25/2022	February	1200.00
27	26	7/2/2022	February	1200.00
28	27	7/9/2022	February	1200.00
29	28	7/16/2022	February	1200.00
30	29	7/23/2022	February	1200.00
31	30	7/30/2022	February	1200.00
32	31	8/6/2022	February	1200.00
33	32	8/13/2022	February	1200.00
34	33	8/20/2022	February	1200.00
35	34	8/27/2022	February	1200.00
36	35	9/3/2022	February	1200.00
37	36	9/10/2022	February	1200.00
38	37	9/17/2022	February	1200.00
39	38	9/24/2022	February	1200.00
40	39	10/1/2022	February	1200.00
41	40	10/8/2022	February	1200.00
42	41	10/15/2022	February	1200.00
43	42	10/22/2022	February	1200.00
44	43	10/29/2022	February	1200.00
45	44	11/5/2022	February	1200.00
46	45	11/12/2022	February	1200.00
47	46	11/19/2022	February	1200.00
48	47	11/26/2022	February	1200.00
49	48	12/3/2022	February	1200.00
50	49	12/10/2022	February	1200.00
51	50	12/17/2022	February	1200.00
52	51	12/24/2022	February	1200.00
53	52	12/31/2022	February	1200.00
54	53	1/7/2023	February	1200.00
55	54	1/14/2023	February	1200.00
56	55	1/21/2023	February	1200.00
57	56	1/28/2023	February	1200.00
58	57	2/4/2023	February	1200.00
59	58	2/11/2023	February	1200.00
60	59	2/18/2023	February	1200.00
61	60	2/25/2023	February	1200.00
62	61	3/4/2023	February	1200.00
63	62	3/11/2023	February	1200.00
64	63	3/18/2023	February	1200.00
65	64	3/25/2023	February	1200.00
66
Extract

Cell Formulas
Range	Formula
B15:B65	B15	=B14+7

Step #3 ??? not sure how the amount column of Sheet 'Extract' gets filled ???

 

[johnnyL]

Please check your formulas in K2:X2 range also.

 

[RAJESH1960]

With the help of resize the cells and filling the K to X columns depending on the number of rows mentioned in column G.

Column G2 is the figure to copy down & fill down the number of rows in column K2, G3 is the figure to copy down & fill down as many row in column L2 and so on.
Column K2 formula is dragged till X2 to get the remaining formulas filled if more than 1 row.
I have shared all the formulas from K2 to X2 in this worksheet with the expected result in column K to X in the Data sheet.

Step #3 ??? not sure how the amount column of Sheet 'Extract' gets filled ???

Loading Google Sheets

 

[RAJESH1960]

I have shown one column of data in column X which is a mistake. It has to be filled down to 365 rows,

 

[RAJESH1960]

Get extract in new sheet.xlsx
	A	B	C	D	E
1	Line	DATE	PARTICULARS	AMOUNT
2	1	4/30/2022	January	80.00
3	2	5/30/2022	January	80.00
4	3	6/30/2022	January	90.00
5	4	7/30/2022	January	70.00
6	5	8/30/2022	January	60.00
7	6	9/30/2022	January	90.00
8	7	10/30/2022	January	70.00
9	8	11/30/2022	January	80.00
10	9	12/30/2022	January	70.00
11	10	1/30/2023	January	60.00
12	11	2/28/2023	January	70.00
13	12	3/30/2023	January	70.00
14	13	4/2/2022	February	1200.00
15	14	4/9/2022	February	1200.00
16	15	4/16/2022	February	1200.00
17	16	4/23/2022	February	1200.00
18	17	4/30/2022	February	1200.00
19	18	5/7/2022	February	1200.00
20	19	5/14/2022	February	1200.00
21	20	5/21/2022	February	1200.00
22	21	5/28/2022	February	1200.00
23	22	6/4/2022	February	1200.00
24	23	6/11/2022	February	1200.00
25	24	6/18/2022	February	1200.00
26	25	6/25/2022	February	1200.00
27	26	7/2/2022	February	1200.00
28	27	7/9/2022	February	1200.00
29	28	7/16/2022	February	1200.00
30	29	7/23/2022	February	1200.00
31	30	7/30/2022	February	1200.00
32	31	8/6/2022	February	1200.00
33	32	8/13/2022	February	1200.00
34	33	8/20/2022	February	1200.00
35	34	8/27/2022	February	1200.00
36	35	9/3/2022	February	1200.00
37	36	9/10/2022	February	1200.00
38	37	9/17/2022	February	1200.00
39	38	9/24/2022	February	1200.00
40	39	10/1/2022	February	1200.00
41	40	10/8/2022	February	1200.00
42	41	10/15/2022	February	1200.00
43	42	10/22/2022	February	1200.00
44	43	10/29/2022	February	1200.00
45	44	11/5/2022	February	1200.00
46	45	11/12/2022	February	1200.00
47	46	11/19/2022	February	1200.00
48	47	11/26/2022	February	1200.00
49	48	12/3/2022	February	1200.00
50	49	12/10/2022	February	1200.00
51	50	12/17/2022	February	1200.00
52	51	12/24/2022	February	1200.00
53	52	12/31/2022	February	1200.00
54	53	1/7/2023	February	1200.00
55	54	1/14/2023	February	1200.00
56	55	1/21/2023	February	1200.00
57	56	1/28/2023	February	1200.00
58	57	2/4/2023	February	1200.00
59	58	2/11/2023	February	1200.00
60	59	2/18/2023	February	1200.00
61	60	2/25/2023	February	1200.00
62	61	3/4/2023	February	1200.00
63	62	3/11/2023	February	1200.00
64	63	3/18/2023	February	1200.00
65	64	3/25/2023	February	1200.00
66
Extract

Cell Formulas
Range	Formula
B15:B65	B15	=B14+7

Step #3 ??? not sure how the amount column of Sheet 'Extract' gets filled ???

The code has to do that. The amount column will get the amounts from the columns K to X. The problem must be the dates. If it is 7 then how will you get the dates. The biggest challenge are the rows 8 and 9, as it has to enter the first date as shown in row B8 and then add 15 to that date in the next row. Then get the first day and the next month in the 3rd row and in the 4th row again get 3rd row + 15 days and so on.

 

[RAJESH1960]

The going is getting tougher and more challenging. Don't expect me to ask you any more easy questions. That I can refer your comments and do it myself.

 

[RAJESH1960]

I can replace the dates and number of row 8 and 9 in column G to get the same result as shown in 11,12,13,14.

 

[RAJESH1960]

Please note: Each time I want to extract the values the number of rows containing data from B to G will not always be 15 rows. It can be 2 to 15 rows each time.