Count, Index, match and Select

[Anbuselvam K]

Hi,

I have 12 no of products with different formulation and the formulation by 12 no of Raw Materials, each raw materials have unique code. In attachment sheet name Formulation you will find the same.

I have to prepare the batch card as in the attached sheet name Batch Card.

If i type the product name then i want to get the batch card sheet as attached.

Can you suggest proper formula to get the RM Code (A8 to A10), Raw Material name (B8 to B10) and Appropriate percentage (E8 to E10) in Batch Card sheet, from formulation sheet.

One more challenge is, Some product have only three raw materials but some have 4, 6 and more.

Please do the needful! Thanks in Advance!

 

[Vishal Srivastava]

Hi,

I have 12 no of products with different formulation and the formulation by 12 no of Raw Materials, each raw materials have unique code. In attachment sheet name Formulation you will find the same.

I have to prepare the batch card as in the attached sheet name Batch Card.

If i type the product name then i want to get the batch card sheet as attached.

Can you suggest proper formula to get the RM Code (A8 to A10), Raw Material name (B8 to B10) and Appropriate percentage (E8 to E10) in Batch Card sheet, from formulation sheet.

One more challenge is, Some product have only three raw materials but some have 4, 6 and more.

Please do the needful! Thanks in Advance!

Please find the solution in attached sheet.

Hope you will find it useful.

Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Thanks a lot!

 

[Anbuselvam K]

Mr Vishal

As per your attached file i have got the results, But i've added one more condition of origin next to the product name, After several trying i didn't get the results properly. the error sheet attached here for your suggestion or modification.

Please do the needful!

 

[Vishal Srivastava]

Mr Vishal

As per your attached file i have got the results, But i've added one more condition of origin next to the product name, After several trying i didn't get the results properly. the error sheet attached here for your suggestion or modification.

Please do the needful!

Hi Anbuselvam,

Just small tweak needed.

I have made the changes. Please find the same in attached sheet.


Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Mr Vishal

I've enter the below formulation and i press control shift enter but i didn't get the results what you got in previous attachment. Please check the formula and suggest.

I didn't understand the -1 and -2 in the formulation before Row function, also what is 1:1 in the row function? please explain for me so that i can adopt the formulation accordingly.

=IFERROR(INDEX(Formulation!$C$2:$AE$2,,SMALL(IF(Formulation!$A$5:$A$118&Formulation!$B$5:$B$118='Batch Card'!$D$5:$E$5&'Batch Card'!$F$5,IF(Formulation!$C$5:$AE$157<>"",COLUMN(Formulation!$C$2:$AE$2)-2)),ROW(1:1))),"")

Thanks in Advance!

 

[Vishal Srivastava]

Mr Vishal

I've enter the below formulation and i press control shift enter but i didn't get the results what you got in previous attachment. Please check the formula and suggest.

I didn't understand the -1 and -2 in the formulation before Row function, also what is 1:1 in the row function? please explain for me so that i can adopt the formulation accordingly.

=IFERROR(INDEX(Formulation!$C$2:$AE$2,,SMALL(IF(Formulation!$A$5:$A$118&Formulation!$B$5:$B$118='Batch Card'!$D$5:$E$5&'Batch Card'!$F$5,IF(Formulation!$C$5:$AE$157<>"",COLUMN(Formulation!$C$2:$AE$2)-2)),ROW(1:1))),"")

Thanks in Advance!

Which type of error you are getting now?

I am getting exact results as per your need.

You may post sample workbook.

CMA Vishal Srivastava

 

[Vishal Srivastava]

For your further understanding, pls have a look at:

http://chandoo.org/wp/2014/12/09/multiple-occurrences-lookup-and-extraction/

Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Mr. Vishal

Please check the file and suggest to fix the error.

 

[Vishal Srivastava]

Mr. Vishal

Please check the file and suggest to fix the error.

Try following formula and drag it down:

{=IFERROR(INDEX(Formulation!$C$2:$AE$2,,SMALL(IF(Formulation!$A$6:$A$17&Formulation!$B$6:$B$17='Batch Card'!$D$5&'Batch Card'!$F$5,IF(Formulation!$C$6:$AE$17<>"",COLUMN(Formulation!$C$2:$AE$2)-2)),ROW(1:1))),"")}

Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Mr Vishal

Sorry to disturb you!

As you suggest i've copy and paste the same, find the attached sheet but i didn't get the result.

Please do the needful!

 

[Vishal Srivastava]

Please find the result in attachment above.

Modify the code accordingly.

Hope you will get answer.

Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Mr Vishal

Thanks for your valuable reply

As per last attached sheet i've changed the product name and its not working!

please take a look once again!

 

[Vishal Srivastava]

Mr Vishal

Thanks for your valuable reply

As per last attached sheet i've changed the product name and its not working!

please take a look once again!

Hi,

I have rechecked the file and modified the formula as per your need.

Please find the same as an attachment above.

Please feel free to contact for any further query.

Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Hi,

Again i've selected the different product name from drop down list but its not working. please check the attachment

 

[Vishal Srivastava]

Hi,

Again i've selected the different product name from drop down list but its not working. please check the attachment

Hi,

Thanks for the update.

I have made small modification to the formula.

Please check and confirm whether it resolves your issue or not.


Thanks & Regards,
CMA Vishal Srivastava

 

[Anbuselvam K]

Got it!

Thanks a lot!

 

[Vishal Srivastava]

Got it!

Thanks a lot!

Glad to know you find the solution


Thanks & Regards,
CMA Vishal Srivastava