aayush_agarwal
New Member
- Joined
- Feb 24, 2019
- Messages
- 10
Hi everyone,
I am new to excel codng and require your help in solving the problem i am facing.
My data set contains different project (10 in total) with each project containing multiple materials (M1,M2,M3,M4...) under it. In front of each material is its corresponding volume. Note that inside a project the name of material may be repeated multiple number of times. My dataset looks similar to the data in the snapshot attached in the pic.
Sheet 1 : Input
<tbody>
</tbody>
I was looking for a formula or VBA code which would calculate the total volume corresponding to each material (M1,M2,M3,M4...) for each project (Project 1, project 2, project 3....). The output required in sheet 2 should look similar to the table shown below :
Output in sheet 2 :
<tbody>
</tbody>
I have understood that i will have to use Vlookup formula with 2 search criteria, however i dont know how to take all volumes of a material inside a project. Fore example, in project 1 material M1 is repeated 2 times with respective volume V1 and V5. I want the output to be V1+V5 whereas Vlookup gives only the first occurance that is V1.
It would be great if you can give a formula or VBA code for solving this problem.
Thanks a lot for your help.
I am new to excel codng and require your help in solving the problem i am facing.
My data set contains different project (10 in total) with each project containing multiple materials (M1,M2,M3,M4...) under it. In front of each material is its corresponding volume. Note that inside a project the name of material may be repeated multiple number of times. My dataset looks similar to the data in the snapshot attached in the pic.
Sheet 1 : Input
| Project | Material | Volume | |
| Project 1 | M1 | V1 | |
| Project 1 | M2 | V2 | |
| Project 1 | M2 | V3 | |
| Project 1 | M4 | V4 | |
| Project 1 | M1 | V5 | |
| Project 1 | M3 | V6 | |
| Project 1 | M4 | V7 | |
| Project 1 | M3 | V8 | |
| Project 2 | M1 | V9 | |
| Project 2 | M2 | V10 | |
| Project 2 | M2 | V11 | |
| Project 2 | M4 | V12 | |
| Project 2 | M1 | V13 | |
| Project 2 | M3 | V14 | |
| Project 2 | M4 | V15 | |
| Project 2 | M1 | V16 |
<tbody>
</tbody>
I was looking for a formula or VBA code which would calculate the total volume corresponding to each material (M1,M2,M3,M4...) for each project (Project 1, project 2, project 3....). The output required in sheet 2 should look similar to the table shown below :
Output in sheet 2 :
| Project | Material | Total Volume | |
| Project 1 | M1 | V1+V5 | |
| Project 1 | M2 | V2+V3 | |
| Project 1 | M3 | V6+V8 | |
| Project 1 | M4 | V4+V7 | |
| Project 2 | M1 | V9+V13+V16 | |
| Project 2 | M2 | V10+V11 | |
| Project 2 | M3 | V14 | |
| Project 2 | M4 | V12+V15 |
<tbody>
</tbody>
I have understood that i will have to use Vlookup formula with 2 search criteria, however i dont know how to take all volumes of a material inside a project. Fore example, in project 1 material M1 is repeated 2 times with respective volume V1 and V5. I want the output to be V1+V5 whereas Vlookup gives only the first occurance that is V1.
It would be great if you can give a formula or VBA code for solving this problem.
Thanks a lot for your help.
