Lookup Material Name Material Unit/Day Consumed Duration and accumulate and compact abstract to the destination

[abuharvey]

Dear Genius/Excel Guru's, Its very difficult to explain, I have attached mini sheet please kindly look in to this sheet, and give me the Formula.

Requet Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	This is the master data -(A)
2	Item No	Description of work	BOQ QTY	Qty	Perday Output	Actual Duration	Rounded Duration	Planned Start	Planned Finish	Bricks	Cement	Fine Sand	Mason	Helper	W-Helper	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Mixer	Vibrator
3	1	Brick Work CM 1:4	35	M3	10.00	3.50	4	16-Oct-24	19-Oct-24	494.00	0.10	0.27	0.72	0.20	1.37	-	-	-	-	-
4	2	Brick Work CM 1:6	75	M3	20.00	3.75	4	19-Oct-24	22-Oct-24	494.00	0.06	0.27	0.72	0.20	1.37	-	-	-	-	-
5	3	RCC 1:2:4	55	M3	25.00	2.20	3	22-Oct-24	24-Oct-24	-	0.32	-	0.17	2.00	0.90	0.67	0.22	0.45	0.07	0.07
6
7		Work Items	Qty		One Day Output			Start	Finish	Material Constant For 1 Unit of the work Items
8
9	Total Quantity Statement Drived from Above Given data (B)
10										Bricks	Cement	Fine Sand	Mason	Helper	W-Helper	Stone aggregate 20mm	Stone aggregate 10mm	Sand	Mixer	Vibrator
11										17,290.00	3.33	9.36	25.20	7.00	47.95	-	-	-	-	-
12										37,050.00	4.69	20.06	54.00	15.00	102.75	-	-	-	-	-
13										-	17.60	-	9.35	110.00	49.50	36.85	12.10	24.48	3.85	3.85
14									Total Qty	54,340.00	25.61	29.43	88.55	132.00	200.20	36.85	12.10	24.48	3.85	3.85
15
16	Material Requirement Date wise as described as per above data( C )
17	Sl.No.	Material Description					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
18	1	Bricks	For Item -1					4,940.00	4940	4940	2470
19	2	Bricks	For Item -2								9,880.00	9,880.00	9,880.00	7,410.00
20
21
22
23	Lookup the Start Date and Duration of the activity Consumed Quantity of that period to be ABSTRACTED As per timeline Direct Formula by skipping Step B and Step C
24	Sl.No.	Material Description	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
25	1	Bricks		4940	4940	4940	12350	9880	9880	7410
26	2	Cement
27	3	Fine Sand
28	4	Mason
29	5	Helper
30	6	W-Helper
31	7	Stone aggregate 20mm
32	8	Stone aggregate 10mm
33	9	Sand
34	10	Mixer
35	11	Vibrator
36
37
38
39
Abs

Cell Formulas
Range	Formula
F3:F5	F3	=+C3/E3
G3:G5	G3	=ROUNDUP(C3/E3,0)
I3:I5	I3	=H3+G3-1
J3:T5	J3	=IF(ISNA(INDEX(RA!$E$1:$E$78,MATCH(1,(RA!$A$1:$A$78=$B3)*(RA!$C$1:$C$78=J$2),0))),0,INDEX(RA!$E$1:$E$78,MATCH(1,(RA!$A$1:$A$78=$B3)*(RA!$C$1:$C$78=J$2),0)))
J11:T13	J11	=J3*$C3
J14:T14	J14	=SUM(J11:J13)
H18	H18	=E3*J3
K18	K18	=0.5*J18
K19	K19	=E4*J4
L19	L19	=K19
M19	M19	=+L19
N19	N19	=0.75*M19
G25	G25	=(F25/2)+20*494
H25:I25	H25	=20*494
J25	J25	=0.75*20*494

in Section (A) my prime data

in Section (B) total Material consumed data this is simply multiply with material coefficient with BOQ Qty

in Section C Material consumption per day basis distribution

Abstract Template is Section (D) I show the conversions in B and C by skipping B an C Steps take the data from A and abstract to D Directly. I don't know it is possible or not. This is my requiremet

 

[hagia_sofia]

Hello, please test the following if it does what you are looking for:

=LET(
o,$E$3:$E$4,
d,$F$3:$F$4,
s,$H$3:$H$4,
f,$I$3:$I$4,
q,$J$3:$T$4,
m,$J$2:$T$2,
material,$B25,
day,C$24,
a,FILTER(FILTER(q,m=material),(s<=day)*(f>=day)),
b,FILTER(IF(day<>f,1,IF((day=f)*(MOD(d,1))=0,1,MOD(d,1))),(s<=day)*(f>=day)),
c,FILTER(o,(s<=day)*(f>=day)),
IFERROR(SUM(a*b*c),0))

 

[abuharvey]

Hello, please test the following if it does what you are looking for:

=LET(
o,$E$3:$E$4,
d,$F$3:$F$4,
s,$H$3:$H$4,
f,$I$3:$I$4,
q,$J$3:$T$4,
m,$J$2:$T$2,
material,$B25,
day,C$24,
a,FILTER(FILTER(q,m=material),(s<=day)*(f>=day)),
b,FILTER(IF(day<>f,1,IF((day=f)*(MOD(d,1))=0,1,MOD(d,1))),(s<=day)*(f>=day)),
c,FILTER(o,(s<=day)*(f>=day)),
IFERROR(SUM(a*b*c),0))

Many More thanks you, Thanks a lot