Dear Sir, This is my data sheet
First Activity Starts at 16-Oct-2024 and finishs at 19-Oct-2024 For that activity we need Brick, cement, Fine Sand, Mason, Etc as Shown in Material Constant
16-oct 10units work will be done
for that 10 units we need 4940 Bricks 1 Cement 2.7 mason Etc
and 19th Oct the remaining Qty is only 5 units for that 2470 Bricks 0.5 Cement likewise. this to be on the abstract as shown timeline here....
One more chalange is the another items are also having same materials that also to be taken togther and added for that particular duration (if falls on the same timeline)
I need the Extraction of this in another sheet like this
BOQ QTY Multiplied with Per day Quantity and with Material Constant that is the to be distributed MAterial Quantity, and it should be distributed to the timeline as mentioned in Planned Start and Finish date (Should not be shared equally to the Duration) The last day if it is in decimels that to be there in the last day.
Materils are shared with many work Items there will be the repetation and to be accumalated and distributed to the timeline as per day use.
I have Entered the values of Brick only Manually
I need Formula For this Thanks All For Your Valuable Time
| Asking_Daniel.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | Sl 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 | ||
| 2 | 1 | Brick Work CM 1:4 | 35 | M3 | 10.00 | 3.50 | 4.00 | 16-Oct-24 | 19-Oct-24 | 494.00 | 0.10 | 0.27 | 0.72 | 0.20 | 1.37 | - | - | - | - | - | ||
| 3 | 2 | Brick Work CM 1:6 | 75 | M3 | 20.00 | 3.75 | 4.00 | 19-Oct-24 | 22-Oct-24 | 494.00 | 0.06 | 0.27 | 0.72 | 0.20 | 1.37 | - | - | - | - | - | ||
| 4 | 3 | RCC 1:2:4 | 55 | M3 | 25.00 | 2.20 | 3.00 | 22-Oct-24 | 24-Oct-24 | - | 0.32 | - | 0.17 | 2.00 | 0.90 | 0.67 | 0.22 | 0.45 | 0.07 | 0.07 | ||
| 5 | ||||||||||||||||||||||
| 6 | Work Items | Qty | One Day Output | Start | Finish | Material Constant For 1 Unit of the work Items | ||||||||||||||||
Abs | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F4 | F2 | =+C2/E2 |
| G2:G4 | G2 | =ROUNDUP(C2/E2,0) |
| I2:I4 | I2 | =H2+G2-1 |
| J2:T4 | J2 | =IF(ISNA(INDEX(RA!$E:$E,MATCH(1,(RA!$A:$A=$B2)*(RA!$C:$C=J$1),0))),0,INDEX(RA!$E:$E,MATCH(1,(RA!$A:$A=$B2)*(RA!$C:$C=J$1),0))) |
First Activity Starts at 16-Oct-2024 and finishs at 19-Oct-2024 For that activity we need Brick, cement, Fine Sand, Mason, Etc as Shown in Material Constant
16-oct 10units work will be done
for that 10 units we need 4940 Bricks 1 Cement 2.7 mason Etc
and 19th Oct the remaining Qty is only 5 units for that 2470 Bricks 0.5 Cement likewise. this to be on the abstract as shown timeline here....
One more chalange is the another items are also having same materials that also to be taken togther and added for that particular duration (if falls on the same timeline)
I need the Extraction of this in another sheet like this
| Asking_Daniel.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | 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 | ||
| 2 | 1 | Bricks | 4940 | 4940 | 4940 | 12350 | 9880 | 9880 | 7410 | |||||||||
| 3 | 2 | Cement | ||||||||||||||||
| 4 | 3 | Fine Sand | ||||||||||||||||
| 5 | 4 | Mason | ||||||||||||||||
| 6 | 5 | Helper | ||||||||||||||||
| 7 | 6 | W-Helper | ||||||||||||||||
| 8 | 7 | Stone aggregate 20mm | ||||||||||||||||
| 9 | 8 | Stone aggregate 10mm | ||||||||||||||||
| 10 | 9 | Sand | ||||||||||||||||
| 11 | 10 | Mixer | ||||||||||||||||
| 12 | 11 | Vibrator | ||||||||||||||||
Mat_Master | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B12 | B2 | =TRANSPOSE(SORT(Abs!J1:T1)) |
| Dynamic array formulas. | ||
BOQ QTY Multiplied with Per day Quantity and with Material Constant that is the to be distributed MAterial Quantity, and it should be distributed to the timeline as mentioned in Planned Start and Finish date (Should not be shared equally to the Duration) The last day if it is in decimels that to be there in the last day.
Materils are shared with many work Items there will be the repetation and to be accumalated and distributed to the timeline as per day use.
I have Entered the values of Brick only Manually
I need Formula For this Thanks All For Your Valuable Time
