Anbuselvam
Board Regular
- Joined
- May 10, 2017
- Messages
- 97
Dear All,
Please find the excel sheet which is having Master sheet, permanent and teporary sheets.
Master sheet is output sheet
Temporary and permanent are my product formulation sheet.
I need to extract formulations from those two sheets to master sheet with a contions of Product name and creation date of product formulation.
For getting the results, i have some formulation and i did not get desired value.
Please check the attachments and do the needfull.
Note: In master sheet i have added expected results too for your study.
Sincerely yours
Anbuselvam K
Attachments:
Permanent
<tbody>
</tbody>
Temporary
<tbody>
</tbody>
Master Sheet
<tbody>
</tbody>
Master Sheet C2 formula is
=IF($P4 = 0, INDEX(Permanent!$C$3:$L$14, $O4, MATCH(C$3, Permanent!$C$2:$L$2, 0)), INDEX(Temporary!$C$3:$L$14, $P4, MATCH(C$3, Temporary!$C$2:$L$2, 0)))
Across and down.
Expected Results in Master Sheet
<tbody>
</tbody>
Please find the excel sheet which is having Master sheet, permanent and teporary sheets.
Master sheet is output sheet
Temporary and permanent are my product formulation sheet.
I need to extract formulations from those two sheets to master sheet with a contions of Product name and creation date of product formulation.
For getting the results, i have some formulation and i did not get desired value.
Please check the attachments and do the needfull.
Note: In master sheet i have added expected results too for your study.
Sincerely yours
Anbuselvam K
Attachments:
Permanent
Date | Product | Incredient 1 | Incredient 2 | Incredient 4 | Incredient 5 | Incredient 6 | Incredient 7 | Incredient 8 | Incredient 10 | Incredient 11 | Incredient 12 |
19-Mar-15 | Vitamin 12 | 13.13% | 2.99% | 19.91% | 0.01% | 0.09% | |||||
19-Mar-15 | Vitamin 1 | 66.52% | 3.00% | 30.36% | 0.02% | 0.10% | |||||
27-Jun-15 | Vitamin 2 | 56.93% | 17.86% | 3.22% | 21.78% | 0.08% | 0.02% | 0.10% | |||
19-Mar-15 | Vitamin 3 | 7.46% | 86.60% | 2.97% | 2.97% | 0.01% | |||||
19-Mar-15 | Vitamin 4 | 10.23% | 89.77% | 0.00% | |||||||
19-Mar-15 | Vitamin 5 | 18.63% | 73.62% | 3.95% | 3.80% | ||||||
19-Mar-15 | Vitamin 6 | 10.95% | 81.24% | 3.46% | 4.35% | ||||||
19-Mar-15 | Vitamin 7 | 7.74% | 85.26% | 3.00% | 4.00% | 0.00% | |||||
19-Mar-15 | Vitamin 8 | 5.00% | 94.60% | 0.40% | |||||||
19-Mar-15 | Vitamin 9 | 12.16% | 80.08% | 3.37% | 4.39% | 0.00% | |||||
19-Mar-15 | Vitamin 10 | 8.10% | 83.59% | 2.97% | 5.34% | ||||||
19-Mar-15 | Vitamin 11 | 96.65% | 2.75% | 0.60% |
<tbody>
</tbody>
Temporary
Date | Product | Incredient 1 | Incredient 2 | Incredient 4 | Incredient 5 | Incredient 6 | Incredient 7 | Incredient 8 | Incredient 10 | Incredient 11 | Incredient 12 |
2-May-16 | Vitamin 12 | 63.87% | 13.13% | 1.00% | 22.00% | 6.00% | 0.09% | ||||
2-May-15 | Vitamin 100 | 66.52% | 3.00% | 30.36% | 0.02% | 0.10% | |||||
2-May-15 | Vitamin 101 | 56.93% | 17.86% | 3.22% | 21.78% | 0.08% | 0.02% | 0.10% | |||
2-May-15 | Vitamin 102 | 7.46% | 86.60% | 2.97% | 2.97% | 0.01% | |||||
2-May-15 | Vitamin 103 | 10.23% | 89.77% | 0.00% | |||||||
2-May-15 | Vitamin 104 | 18.63% | 73.62% | 3.95% | 3.80% | ||||||
2-May-15 | Vitamin 105 | 10.95% | 81.24% | 3.46% | 4.35% | ||||||
2-May-15 | Vitamin 106 | 7.74% | 85.26% | 3.00% | 4.00% | 0.00% | |||||
2-May-15 | Vitamin 107 | 5.00% | 94.60% | 0.40% | |||||||
2-May-15 | Vitamin 108 | 12.16% | 80.08% | 3.37% | 4.39% | 0.00% | |||||
2-May-15 | Vitamin 109 | 100.00% | |||||||||
2-May-15 | Vitamin 110 | 96.65% | 2.75% | 0.60% |
<tbody>
</tbody>
Master Sheet
Date | Product | Incredient 1 | Incredient 2 | Incredient 4 | Incredient 5 | Incredient 6 | Incredient 7 | Incredient 8 | Incredient 10 | Incredient 11 | Incredient 12 |
19-Mar-13 | Vitamin 12 | 66.52% | 0.00% | 3.00% | 30.36% | 0.00% | 0.02% | 0.10% | 0.00% | 0.00% | 0.00% |
19-Mar-15 | Vitamin 12 | 56.93% | 17.86% | 3.22% | 21.78% | 0.08% | 0.02% | 0.10% | 0.00% | 0.00% | 0.00% |
23-Mar-15 | Vitamin 12 | 7.46% | 86.60% | 2.97% | 2.97% | 0.00% | 0.01% | 0.00% | 0.00% | 0.00% | 0.00% |
19-Mar-16 | Vitamin 12 | 10.23% | 89.77% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
21-Mar-16 | Vitamin 12 | 18.63% | 73.62% | 3.95% | 3.80% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
1-May-16 | Vitamin 12 | 10.95% | 81.24% | 3.46% | 4.35% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
5-May-17 | Vitamin 12 | 7.74% | 85.26% | 3.00% | 4.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
21-May-17 | Vitamin 12 | 5.00% | 94.60% | 0.00% | 0.00% | 0.40% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
23-May-17 | Vitamin 12 | 12.16% | 80.08% | 3.37% | 4.39% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
<tbody>
</tbody>
Master Sheet C2 formula is
=IF($P4 = 0, INDEX(Permanent!$C$3:$L$14, $O4, MATCH(C$3, Permanent!$C$2:$L$2, 0)), INDEX(Temporary!$C$3:$L$14, $P4, MATCH(C$3, Temporary!$C$2:$L$2, 0)))
Across and down.
Expected Results in Master Sheet
Date | Product | Incredient 1 | Incredient 2 | Incredient 4 | Incredient 5 | Incredient 6 | Incredient 7 | Incredient 8 | Incredient 10 | Incredient 11 | Incredient 12 |
19-Mar-13 | Vitamin 12 | 0.13127 | 0.02987 | 0.19914 | 0.0001 | 0.0009 | |||||
19-Mar-15 | Vitamin 12 | 0.13127 | 0.02987 | 0.19914 | 0.0001 | 0.0009 | |||||
23-Mar-15 | Vitamin 12 | 0.13127 | 0.02987 | 0.19914 | 0.0001 | 0.0009 | |||||
19-Mar-16 | Vitamin 12 | 0.13127 | 0.02987 | 0.19914 | 0.0001 | 0.0009 | |||||
21-Mar-16 | Vitamin 12 | 0.13127 | 0.02987 | 0.19914 | 0.0001 | 0.0009 | |||||
1-May-16 | Vitamin 12 | 0.13127 | 0.02987 | 0.19914 | 0.0001 | 0.0009 | |||||
5-May-17 | Vitamin 12 | 0.63872 | 0.13127 | 0.01 | 0.22 | 0.06 | 0.0009 | ||||
21-May-17 | Vitamin 12 | 0.63872 | 0.13127 | 0.01 | 0.22 | 0.06 | 0.0009 | ||||
23-May-17 | Vitamin 12 | 0.63872 | 0.13127 | 0.01 | 0.22 | 0.06 | 0.0009 |
<tbody>
</tbody>