Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta

[abuharvey]

Dear Genius, I need a solution for this

Lookup value in Sheet2 Cell B2, Goto the Sheet1 Match the Value in Row 1 From Cell G1 to Cell O1, Find The total Consumed Qty, and Find the Duration in Column P And Q Bring into Sheet2 Spread the Qty in the timeline starts from D1 Our Formula should be Stat from Sheet2 Cell D2

This Data in Sheet1

$G$1	$H$1	$I$1	$J$1	$K$1	$L$1	$M$1	$N$1	$O$1	$P$1	$Q$1
Bricks	Cement	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Fine Sand	Mason	Helper	W-Helper	Planned Start	Planned Finish
44,460.00	8.55	-	-	-	24.08	64.80	18.00	123.30	16-Oct-24	21-Oct-24
74,100.00	9.38	-	-	-	40.13	108.00	30.00	205.50	19-Oct-24	25-Oct-24
-	34.16	35.84	11.76	23.80	-	9.52	112.00	77.28	22-Oct-24	24-Oct-24
-	6.81	-	-	-	19.18	83.42	93.38	114.54	26-Oct-24	24-Nov-24

In Sheet2 I need this results

Sl.No.	Material Description	Unit	10-10-2024	11-10-2024	12-10-2024	13-10-2024	14-10-2024	15-10-2024	16-10-2024	17-10-2024	18-10-2024	19-10-2024	20-10-2024	21-10-2024	22-10-2024	23-10-2024	24-10-2024	25-10-2024	26-10-2024
1	Bricks	Nos							7,410.00	7,410.00	7,410.00	18,278.00	18,278.00	18,278.00	10,868.00	10,868.00	10,868.00	8,892.00
2	Cement	Bag
3	Stone aggregate 20mm	Cum
4	Stone aggregate 10mm	Cum
5	Sand	Cum
6	Fine Sand	Cum
7	Mason	Day
8	Helper	Day
9	W-Helper	Day
			I need Formula For this Statement

Thanks In Advance

 

[DanteAmor]

In your example,
The bricks of 44,460 divided by 6 is equal to 7,410 and you distribute it over 6 days. But I don't understand why the bricks of 74,100 divided by 7 is equal to 10585.71, but the amount you distribute is another value and on the last day you put another value.

If it is an error in your example, then the result should be like this.

First on sheet1. Help me with an auxiliary column with the formula =Q2-P2+1 cells R2 to R5:

Dante Amor
	G	H	I	J	K	L	M	N	O	P	Q	R
1	Bricks	Cement	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Fine Sand	Mason	Helper	W-Helper	Planned Start	Planned Finish	Days (help column)
2	44,460.00	8.55	0.00	0.00	0.00	24.08	64.80	18.00	123.30	16/10/2024	21/10/2024	6
3	74,100.00	9.38	0.00	0.00	0.00	40.13	108.00	30.00	205.50	19/10/2024	25/10/2024	7
4	0.00	34.16	35.84	11.76	23.80	0.00	9.52	112.00	77.28	22/10/2024	24/10/2024	3
5	0.00	6.81	0.00	0.00	0.00	19.18	83.42	93.38	114.54	26/10/2024	24/11/2024	30
6	118,560.00	58.90	35.84	11.76	23.80	83.39	265.74	253.38	520.62			119,813.43
Sheet1

Cell Formulas
Range	Formula
G6:O6	G6	=SUM(G2:G5)
R2:R5	R2	=Q2-P2+1
R6	R6	=SUM(G6:O6)

Note: I only put the totals to check the results.

On Sheet2:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T																													AW	AX
1	Sl.No.	Material Description	Unit	10/10/2024	11/10/2024	12/10/2024	13/10/2024	14/10/2024	15/10/2024	16/10/2024	17/10/2024	18/10/2024	19/10/2024	20/10/2024	21/10/2024	22/10/2024	23/10/2024	24/10/2024	25/10/2024	26/10/2024																													24/11/2024	TOTAL
2	1	Bricks	Nos	0.00	0.00	0.00	0.00	0.00	0.00	7,410.00	7,410.00	7,410.00	17,995.71	17,995.71	17,995.71	10,585.71	10,585.71	10,585.71	10,585.71	0.00																													0.00	118,560.00
3	2	Cement	Bag	0.00	0.00	0.00	0.00	0.00	0.00	1.43	1.43	1.43	2.77	2.77	2.77	12.73	12.73	12.73	1.34	0.23																													0.23	58.90
4	3	Stone aggregate 20mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	11.95	11.95	11.95	0.00	0.00																													0.00	35.84
5	4	Stone aggregate 10mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.92	3.92	3.92	0.00	0.00																													0.00	11.76
6	5	Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	7.93	7.93	7.93	0.00	0.00																													0.00	23.80
7	6	Fine Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	4.01	4.01	4.01	9.75	9.75	9.75	5.73	5.73	5.73	5.73	0.64																													0.64	83.39
8	7	Mason	Day	0.00	0.00	0.00	0.00	0.00	0.00	10.80	10.80	10.80	26.23	26.23	26.23	18.60	18.60	18.60	15.43	2.78																													2.78	265.74
9	8	Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	3.00	3.00	3.00	7.29	7.29	7.29	41.62	41.62	41.62	4.29	3.11																													3.11	253.38
10	9	W-Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	20.55	20.55	20.55	49.91	49.91	49.91	55.12	55.12	55.12	29.36	3.82																													3.82	520.62
11		TOTAL		0.00	0.00	0.00	0.00	0.00	0.00	7,449.79	7,449.79	7,449.79	18,091.65	18,091.65	18,091.65	10,743.31	10,743.31	10,743.31	10,641.86	10.58																													10.58	119,813.43
Sheet2

Cell Formulas
Range	Formula
D2:AW10	D2	=SUMPRODUCT(((Sheet1!$G$1:$O$1=$B2)*(Sheet1!$P$2:$P$5<=D$1)*(Sheet1!$Q$2:$Q$5>=D$1)*(Sheet1!$G$2:$O$5/Sheet1!$R$2:$R$5)))
AX2:AX11	AX2	=SUM(D2:AW2)
AW11,D11:T11	D11	=SUM(D2:D10)

Note: I only put the totals to check the results.
You can verify that the sum of the distributed 119,813.43 is equal to the original sum.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[kvsrinivasamurthy]

Why 74100 Bricks are divided as 10868 for 6 days and remaining 8892 for 7th day.

 

[abuharvey]

Why 74100 Bricks are divided as 10868 for 6 days and remaining 8892 for 7th day.

 

[abuharvey]

In your example,
The bricks of 44,460 divided by 6 is equal to 7,410 and you distribute it over 6 days. But I don't understand why the bricks of 74,100 divided by 7 is equal to 10585.71, but the amount you distribute is another value and on the last day you put another value.

View attachment 118278​

If it is an error in your example, then the result should be like this.

First on sheet1. Help me with an auxiliary column with the formula =Q2-P2+1 cells R2 to R5:

Dante Amor
	G	H	I	J	K	L	M	N	O	P	Q	R
1	Bricks	Cement	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Fine Sand	Mason	Helper	W-Helper	Planned Start	Planned Finish	Days (help column)
2	44,460.00	8.55	0.00	0.00	0.00	24.08	64.80	18.00	123.30	16/10/2024	21/10/2024	6
3	74,100.00	9.38	0.00	0.00	0.00	40.13	108.00	30.00	205.50	19/10/2024	25/10/2024	7
4	0.00	34.16	35.84	11.76	23.80	0.00	9.52	112.00	77.28	22/10/2024	24/10/2024	3
5	0.00	6.81	0.00	0.00	0.00	19.18	83.42	93.38	114.54	26/10/2024	24/11/2024	30
6	118,560.00	58.90	35.84	11.76	23.80	83.39	265.74	253.38	520.62			119,813.43
Sheet1

Cell Formulas
Range	Formula
G6:O6	G6	=SUM(G2:G5)
R2:R5	R2	=Q2-P2+1
R6	R6	=SUM(G6:O6)

Note: I only put the totals to check the results.

On Sheet2:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T																													AW	AX
1	Sl.No.	Material Description	Unit	10/10/2024	11/10/2024	12/10/2024	13/10/2024	14/10/2024	15/10/2024	16/10/2024	17/10/2024	18/10/2024	19/10/2024	20/10/2024	21/10/2024	22/10/2024	23/10/2024	24/10/2024	25/10/2024	26/10/2024																													24/11/2024	TOTAL
2	1	Bricks	Nos	0.00	0.00	0.00	0.00	0.00	0.00	7,410.00	7,410.00	7,410.00	17,995.71	17,995.71	17,995.71	10,585.71	10,585.71	10,585.71	10,585.71	0.00																													0.00	118,560.00
3	2	Cement	Bag	0.00	0.00	0.00	0.00	0.00	0.00	1.43	1.43	1.43	2.77	2.77	2.77	12.73	12.73	12.73	1.34	0.23																													0.23	58.90
4	3	Stone aggregate 20mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	11.95	11.95	11.95	0.00	0.00																													0.00	35.84
5	4	Stone aggregate 10mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.92	3.92	3.92	0.00	0.00																													0.00	11.76
6	5	Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	7.93	7.93	7.93	0.00	0.00																													0.00	23.80
7	6	Fine Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	4.01	4.01	4.01	9.75	9.75	9.75	5.73	5.73	5.73	5.73	0.64																													0.64	83.39
8	7	Mason	Day	0.00	0.00	0.00	0.00	0.00	0.00	10.80	10.80	10.80	26.23	26.23	26.23	18.60	18.60	18.60	15.43	2.78																													2.78	265.74
9	8	Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	3.00	3.00	3.00	7.29	7.29	7.29	41.62	41.62	41.62	4.29	3.11																													3.11	253.38
10	9	W-Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	20.55	20.55	20.55	49.91	49.91	49.91	55.12	55.12	55.12	29.36	3.82																													3.82	520.62
11		TOTAL		0.00	0.00	0.00	0.00	0.00	0.00	7,449.79	7,449.79	7,449.79	18,091.65	18,091.65	18,091.65	10,743.31	10,743.31	10,743.31	10,641.86	10.58																													10.58	119,813.43
Sheet2

Cell Formulas
Range	Formula
D2:AW10	D2	=SUMPRODUCT(((Sheet1!$G$1:$O$1=$B2)*(Sheet1!$P$2:$P$5<=D$1)*(Sheet1!$Q$2:$Q$5>=D$1)*(Sheet1!$G$2:$O$5/Sheet1!$R$2:$R$5)))
AX2:AX11	AX2	=SUM(D2:AW2)
AW11,D11:T11	D11	=SUM(D2:D10)

Note: I only put the totals to check the results.
You can verify that the sum of the distributed 119,813.43 is equal to the original sum.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[abuharvey]

This is the original Data from this data I Create Sheet1 and Sheet2 to simplify this sheet I need the formula

 

[abuharvey]

In your example,
The bricks of 44,460 divided by 6 is equal to 7,410 and you distribute it over 6 days. But I don't understand why the bricks of 74,100 divided by 7 is equal to 10585.71, but the amount you distribute is another value and on the last day you put another value.

View attachment 118278​

If it is an error in your example, then the result should be like this.

First on sheet1. Help me with an auxiliary column with the formula =Q2-P2+1 cells R2 to R5:

Dante Amor
	G	H	I	J	K	L	M	N	O	P	Q	R
1	Bricks	Cement	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Fine Sand	Mason	Helper	W-Helper	Planned Start	Planned Finish	Days (help column)
2	44,460.00	8.55	0.00	0.00	0.00	24.08	64.80	18.00	123.30	16/10/2024	21/10/2024	6
3	74,100.00	9.38	0.00	0.00	0.00	40.13	108.00	30.00	205.50	19/10/2024	25/10/2024	7
4	0.00	34.16	35.84	11.76	23.80	0.00	9.52	112.00	77.28	22/10/2024	24/10/2024	3
5	0.00	6.81	0.00	0.00	0.00	19.18	83.42	93.38	114.54	26/10/2024	24/11/2024	30
6	118,560.00	58.90	35.84	11.76	23.80	83.39	265.74	253.38	520.62			119,813.43
Sheet1

Cell Formulas
Range	Formula
G6:O6	G6	=SUM(G2:G5)
R2:R5	R2	=Q2-P2+1
R6	R6	=SUM(G6:O6)

Note: I only put the totals to check the results.

On Sheet2:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T																													AW	AX
1	Sl.No.	Material Description	Unit	10/10/2024	11/10/2024	12/10/2024	13/10/2024	14/10/2024	15/10/2024	16/10/2024	17/10/2024	18/10/2024	19/10/2024	20/10/2024	21/10/2024	22/10/2024	23/10/2024	24/10/2024	25/10/2024	26/10/2024																													24/11/2024	TOTAL
2	1	Bricks	Nos	0.00	0.00	0.00	0.00	0.00	0.00	7,410.00	7,410.00	7,410.00	17,995.71	17,995.71	17,995.71	10,585.71	10,585.71	10,585.71	10,585.71	0.00																													0.00	118,560.00
3	2	Cement	Bag	0.00	0.00	0.00	0.00	0.00	0.00	1.43	1.43	1.43	2.77	2.77	2.77	12.73	12.73	12.73	1.34	0.23																													0.23	58.90
4	3	Stone aggregate 20mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	11.95	11.95	11.95	0.00	0.00																													0.00	35.84
5	4	Stone aggregate 10mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.92	3.92	3.92	0.00	0.00																													0.00	11.76
6	5	Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	7.93	7.93	7.93	0.00	0.00																													0.00	23.80
7	6	Fine Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	4.01	4.01	4.01	9.75	9.75	9.75	5.73	5.73	5.73	5.73	0.64																													0.64	83.39
8	7	Mason	Day	0.00	0.00	0.00	0.00	0.00	0.00	10.80	10.80	10.80	26.23	26.23	26.23	18.60	18.60	18.60	15.43	2.78																													2.78	265.74
9	8	Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	3.00	3.00	3.00	7.29	7.29	7.29	41.62	41.62	41.62	4.29	3.11																													3.11	253.38
10	9	W-Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	20.55	20.55	20.55	49.91	49.91	49.91	55.12	55.12	55.12	29.36	3.82																													3.82	520.62
11		TOTAL		0.00	0.00	0.00	0.00	0.00	0.00	7,449.79	7,449.79	7,449.79	18,091.65	18,091.65	18,091.65	10,743.31	10,743.31	10,743.31	10,641.86	10.58																													10.58	119,813.43
Sheet2

Cell Formulas
Range	Formula
D2:AW10	D2	=SUMPRODUCT(((Sheet1!$G$1:$O$1=$B2)*(Sheet1!$P$2:$P$5<=D$1)*(Sheet1!$Q$2:$Q$5>=D$1)*(Sheet1!$G$2:$O$5/Sheet1!$R$2:$R$5)))
AX2:AX11	AX2	=SUM(D2:AW2)
AW11,D11:T11	D11	=SUM(D2:D10)

Note: I only put the totals to check the results.
You can verify that the sum of the distributed 119,813.43 is equal to the original sum.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Sir You are Great As Per your Instruction its working well, Actually the explanation which I given is wrong here is the detail working of my excel sheet screen shot I have attached from this basic data I have created Sheet1 and the Sheet2 is only material extract only hope you understand, thanks genius.. how i can upload mini sheet please explain

 

[abuharvey]

Why 74100 Bricks are divided as 10868 for 6 days and remaining 8892 for 7th day.

Sir This is the original working data Excel Sheet From this I came to Sheet1 and Sheet2, Only I need the Material Requirements as Abstract in one sheet, thanks Genius ...

 

[abuharvey]

Why 74100 Bricks are divided as 10868 for 6 days and remaining 8892 for 7th day.

Sl No	Description of work	Material And Manpower	BOQ QTY	Unit	Perday Output	Duration	Unit/Per	Unit	Rate	Start Date	Finish Date	15-10-2024	16-10-2024	17-10-2024	18-10-2024	19-10-2024	20-10-2024	21-10-2024	22-10-2024	23-10-2024	24-10-2024	25-10-2024	26-10-2024	27-10-2024	28-10-2024	T.Qty
1	Brick Work CM 1:4	Bricks	90	M3	15	6.00	494	Nos	2.9	16-Oct-24	21-Oct-24	-	7,410.00	7,410.00	7,410.00	7,410.00	7,410.00	7,410.00	-	-	-	-	-	-	-	44,460.00
2	Brick Work CM 1:4	Cement	150	M3	22	7.00	0.095	tonne	5000	16-Oct-24	22-Oct-24	-	2.09	2.09	2.09	2.09	2.09	2.09	1.71	-	-	-	-	-	-	14.25
3	Brick Work CM 1:4	Fine Sand	96	M3	12	8.00	0.2675	cum	1120	16-Oct-24	23-Oct-24	-	3.21	3.21	3.21	3.21	3.21	3.21	3.21	3.21	-	-	-	-	-	25.68
4	Brick Work CM 1:4	Mason	96	M3	12	8.00	0.72	Day	301	16-Oct-24	23-Oct-24	-	8.64	8.64	8.64	8.64	8.64	8.64	8.64	8.64	-	-	-	-	-	69.12
5	Brick Work CM 1:4	Helper	96	M3	12	8.00	0.2	Day	260	16-Oct-24	23-Oct-24	-	2.40	2.40	2.40	2.40	2.40	2.40	2.40	2.40	-	-	-	-	-	19.20
6	Brick Work CM 1:4	W-Helper	96	M3	12	8.00	1.37	Day	247	16-Oct-24	23-Oct-24	-	16.44	16.44	16.44	16.44	16.44	16.44	16.44	16.44	-	-	-	-	-	131.52
7	Brick Work CM 1:6	Bricks	118	M3	12	10.00	494	Nos	2.9	19-Oct-24	28-Oct-24	-	-	-	-	5,928.00	5,928.00	5,928.00	5,928.00	5,928.00	5,928.00	5,928.00	5,928.00	5,928.00	4,940.00	58,292.00
8	Brick Work CM 1:6	Cement	118	M3	12	10.00	0.0625	tonne	5000	19-Oct-24	28-Oct-24	-	-	-	-	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.63	7.38
9	Brick Work CM 1:6	Fine Sand	118	M3	12	10.00	0.2675	cum	1120	19-Oct-24	28-Oct-24	-	-	-	-	3.21	3.21	3.21	3.21	3.21	3.21	3.21	3.21	3.21	2.68	31.57
10	Brick Work CM 1:6	Mason	118	M3	12	10.00	0.72	Day	301	19-Oct-24	28-Oct-24	-	-	-	-	8.64	8.64	8.64	8.64	8.64	8.64	8.64	8.64	8.64	7.20	84.96
11	Brick Work CM 1:6	Helper	118	M3	12	10.00	0.2	Day	260	19-Oct-24	28-Oct-24	-	-	-	-	2.40	2.40	2.40	2.40	2.40	2.40	2.40	2.40	2.40	2.00	23.60
12	Brick Work CM 1:6	W-Helper	118	M3	12	10.00	1.37	Day	247	19-Oct-24	28-Oct-24	-	-	-	-	16.44	16.44	16.44	16.44	16.44	16.44	16.44	16.44	16.44	13.70	161.66
13	RCC 1:2:4	Stone aggregate 20mm	207	M3	35	6.00	0.67	cum	1050	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	23.45	23.45	23.45	23.45	23.45	21.44	-	138.69
14	RCC 1:2:4	Stone aggregate 10mm	207	M3	35	6.00	0.22	cum	1050	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	7.70	7.70	7.70	7.70	7.70	7.04	-	45.54
15	RCC 1:2:4	Sand	207	M3	35	6.00	0.445	cum	1120	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	15.58	15.58	15.58	15.58	15.58	14.24	-	92.12
16	RCC 1:2:4	Cement	207	M3	35	6.00	0.32	tonne	5000	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	11.20	11.20	11.20	11.20	11.20	10.24	-	66.24
17	RCC 1:2:4	Mason	207	M3	35	6.00	0.17	Day	287	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	5.95	5.95	5.95	5.95	5.95	5.44	-	35.19
18	RCC 1:2:4	Helper	207	M3	35	6.00	2	Day	247	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	70.00	70.00	70.00	70.00	70.00	64.00	-	414.00
19	RCC 1:2:4	W-Helper	207	M3	35	6.00	0.9	Day	260	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	31.50	31.50	31.50	31.50	31.50	28.80	-	186.30
20	RCC 1:2:4	Mixer	207	M3	35	6.00	0.07	Day	800	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	2.45	2.45	2.45	2.45	2.45	2.24	-	14.49
21	RCC 1:2:4	Vibrator	207	M3	35	6.00	0.07	Day	300	22-Oct-24	27-Oct-24	-	-	-	-	-	-	-	2.45	2.45	2.45	2.45	2.45	2.24	-	14.49

Sir this is the data from this I take data to sheet1 and in sheet i need Material Abstract

 

[abuharvey]

In your example,
The bricks of 44,460 divided by 6 is equal to 7,410 and you distribute it over 6 days. But I don't understand why the bricks of 74,100 divided by 7 is equal to 10585.71, but the amount you distribute is another value and on the last day you put another value.

View attachment 118278​

If it is an error in your example, then the result should be like this.

First on sheet1. Help me with an auxiliary column with the formula =Q2-P2+1 cells R2 to R5:

Dante Amor
	G	H	I	J	K	L	M	N	O	P	Q	R
1	Bricks	Cement	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Fine Sand	Mason	Helper	W-Helper	Planned Start	Planned Finish	Days (help column)
2	44,460.00	8.55	0.00	0.00	0.00	24.08	64.80	18.00	123.30	16/10/2024	21/10/2024	6
3	74,100.00	9.38	0.00	0.00	0.00	40.13	108.00	30.00	205.50	19/10/2024	25/10/2024	7
4	0.00	34.16	35.84	11.76	23.80	0.00	9.52	112.00	77.28	22/10/2024	24/10/2024	3
5	0.00	6.81	0.00	0.00	0.00	19.18	83.42	93.38	114.54	26/10/2024	24/11/2024	30
6	118,560.00	58.90	35.84	11.76	23.80	83.39	265.74	253.38	520.62			119,813.43
Sheet1

Cell Formulas
Range	Formula
G6:O6	G6	=SUM(G2:G5)
R2:R5	R2	=Q2-P2+1
R6	R6	=SUM(G6:O6)

Note: I only put the totals to check the results.

On Sheet2:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T																													AW	AX
1	Sl.No.	Material Description	Unit	10/10/2024	11/10/2024	12/10/2024	13/10/2024	14/10/2024	15/10/2024	16/10/2024	17/10/2024	18/10/2024	19/10/2024	20/10/2024	21/10/2024	22/10/2024	23/10/2024	24/10/2024	25/10/2024	26/10/2024																													24/11/2024	TOTAL
2	1	Bricks	Nos	0.00	0.00	0.00	0.00	0.00	0.00	7,410.00	7,410.00	7,410.00	17,995.71	17,995.71	17,995.71	10,585.71	10,585.71	10,585.71	10,585.71	0.00																													0.00	118,560.00
3	2	Cement	Bag	0.00	0.00	0.00	0.00	0.00	0.00	1.43	1.43	1.43	2.77	2.77	2.77	12.73	12.73	12.73	1.34	0.23																													0.23	58.90
4	3	Stone aggregate 20mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	11.95	11.95	11.95	0.00	0.00																													0.00	35.84
5	4	Stone aggregate 10mm	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.92	3.92	3.92	0.00	0.00																													0.00	11.76
6	5	Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	7.93	7.93	7.93	0.00	0.00																													0.00	23.80
7	6	Fine Sand	Cum	0.00	0.00	0.00	0.00	0.00	0.00	4.01	4.01	4.01	9.75	9.75	9.75	5.73	5.73	5.73	5.73	0.64																													0.64	83.39
8	7	Mason	Day	0.00	0.00	0.00	0.00	0.00	0.00	10.80	10.80	10.80	26.23	26.23	26.23	18.60	18.60	18.60	15.43	2.78																													2.78	265.74
9	8	Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	3.00	3.00	3.00	7.29	7.29	7.29	41.62	41.62	41.62	4.29	3.11																													3.11	253.38
10	9	W-Helper	Day	0.00	0.00	0.00	0.00	0.00	0.00	20.55	20.55	20.55	49.91	49.91	49.91	55.12	55.12	55.12	29.36	3.82																													3.82	520.62
11		TOTAL		0.00	0.00	0.00	0.00	0.00	0.00	7,449.79	7,449.79	7,449.79	18,091.65	18,091.65	18,091.65	10,743.31	10,743.31	10,743.31	10,641.86	10.58																													10.58	119,813.43
Sheet2

Cell Formulas
Range	Formula
D2:AW10	D2	=SUMPRODUCT(((Sheet1!$G$1:$O$1=$B2)*(Sheet1!$P$2:$P$5<=D$1)*(Sheet1!$Q$2:$Q$5>=D$1)*(Sheet1!$G$2:$O$5/Sheet1!$R$2:$R$5)))
AX2:AX11	AX2	=SUM(D2:AW2)
AW11,D11:T11	D11	=SUM(D2:D10)

Note: I only put the totals to check the results.
You can verify that the sum of the distributed 119,813.43 is equal to the original sum.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Dear Genius, as per your assumption its working perfect, I have not given my perfect requirement. Here with this I have attached My master sheet as Screen shot, From this sheet I need to extract Material requirement Abstract as per the timeline