Which functions and Procedure to use to have desired result?

[Soham]

Dear Experts,

Following is the my Querry, Can you guide me, please?
Querry pasted, I want to attach Excel sheet, but do not konw, how to attach?
Can help?

I want to prepare a Summary based on the data given on two separate sheet as
follow:

Specification (From A on Separate Sheet)
ORIGIN Material CODE Material NAME
ITALY MF-001 FIOR CARNICO
CHINA MF-002 HONEY ONYX
ITALY MF-003 WHITE STATUARIO

Bill of Quantities (From B on Separate Sheet)
Location Item Description Material CODE Material NAME Rate Quantity Amount
Room1 Cladding A MF-001 FIOR CARNICO 1.00 2.00 2.00
Room1 Cladding A MF-002 HONEY ONYX 3.00 3.00 9.00
Room1 Cladding A MF-003 WHITE STATUARIO 5.00 5.00 25.00
Room1 Cladding B MF-001 FIOR CARNICO 2.00 4.00 8.00
Room1 Cladding B MF-002 HONEY ONYX 4.00 6.00 24.00
Room1 Cladding B MF-003 WHITE STATUARIO 6.00 2.00 12.00
Room1 Flooring A MF-001 FIOR CARNICO 7.00 4.00 28.00
Room1 Flooring A MF-002 HONEY ONYX 9.00 5.00 45.00
Room1 Flooring A MF-003 WHITE STATUARIO 11.00 2.00 22.00
Room1 Flooring B MF-001 FIOR CARNICO 8.00 1.00 8.00
Room1 Flooring B MF-002 HONEY ONYX 10.00 3.00 30.00
Room1 Flooring B MF-003 WHITE STATUARIO 12.00 5.00 60.00


Room2 Cladding A MF-001 FIOR CARNICO 1.00 8.00 8.00
Room2 Cladding A MF-002 HONEY ONYX 3.00 5.00 15.00
Room2 Cladding A MF-003 WHITE STATUARIO 5.00 4.00 20.00
Room2 Cladding B MF-001 FIOR CARNICO 2.00 1.00 2.00
Room2 Cladding B MF-002 HONEY ONYX 4.00 2.00 8.00
Room2 Cladding B MF-003 WHITE STATUARIO 6.00 3.00 18.00
Room2 Flooring A MF-001 FIOR CARNICO 7.00 5.00 35.00
Room2 Flooring A MF-002 HONEY ONYX 9.00 6.00 54.00
Room2 Flooring A MF-003 WHITE STATUARIO 11.00 4.00 44.00
Room2 Flooring B MF-001 FIOR CARNICO 8.00 5.00 40.00
Room2 Flooring B MF-002 HONEY ONYX 10.00 2.00 20.00
Room2 Flooring B MF-003 WHITE STATUARIO 12.00 3.00 36.00

Option 1 - without making Rate table as below]
Summary (I am preparing Manually - on Separate Sheet but want to Prepare
using some function/formula)
Filling Manually Filling Manually Filling Manually Filling Manually Filling
Manually Formula
Item Description Material CODE Material NAME Rate Quantity Amount
Total Cladding A MF-001 FIOR CARNICO 1.00 10.00 10.00
Total Cladding A MF-002 HONEY ONYX 3.00 8.00 24.00
Total Cladding A MF-003 WHITE STATUARIO 5.00 9.00 45.00
Total Cladding B MF-001 FIOR CARNICO 2.00 5.00 10.00
Total Cladding B MF-002 HONEY ONYX 4.00 8.00 32.00
Total Cladding B MF-003 WHITE STATUARIO 6.00 5.00 30.00
Total Flooring A MF-001 FIOR CARNICO 7.00 9.00 63.00
Total Flooring A MF-002 HONEY ONYX 9.00 11.00 99.00
Total Flooring A MF-003 WHITE STATUARIO 11.00 6.00 66.00
Total Flooring B MF-001 FIOR CARNICO 8.00 6.00 48.00
Total Flooring B MF-002 HONEY ONYX 10.00 5.00 50.00
Total Flooring B MF-003 WHITE STATUARIO 12.00 8.00 96.00

Rate Table (I am preparing Manually - on Separate Sheet but want to Prepare
using some function/formula) (Shown in Green Cells
Material CODE --> MF-001 MF-002 MF-003
Material NAME --> FIOR CARNICO HONEY ONYX WHITE STATUARIO
ORIGIN--> ITALY CHINA ITALY
Filling Manually Rates filling Manually Rates filling Manually Rates
filling Manually
Cladding A 1.00 3.00 5.00
Cladding B 2.00 4.00 6.00
Flooring A 7.00 9.00 11.00
Flooring B 8.00 10.00 12.00

Option 2 - with making above Rate table and using the same]
Summary (I am preparing Manually - on Separate Sheet but want to Prepare
using some function/formula)

Thanking you in advance for guidance!

Waiting for reply>

Soham