Multiple values returned based on two conditions in a matrix

[ah2931]

Struggling with this one - I have a matrix/grid type setup for an entire product ranges' bills of materials. What I want to do is return every named instance of an individual raw material, based on matching a part number. In addition, it needs to return said raw material name only if the usage is greater than 0. In every case, there will be more than one raw material going in to each part.

Raw materials are in B2:I2, Part numbers in A3:A12. Usages in B3:I12.

Plan would be to have a summary type sheet that will allow the part number to be keyed in, which returns all raw materials that go in to the product. Am currently at a complete loss as to how to accomplish this! Tried various combinations of array formulas, but have had no luck tweaking other people's examples for my purposes. Also unclear as to whether this is best achieved using formulas, or VBA.

Example of the layout below:

So on a summary sheet Cell A1 for e.g. the part number would be entered: returning in column B all of the raw materials that comprise that part.
- So for Part 2, I'd be aiming to see Raw Material 1 in B1, Raw Material 6 in B2 & Raw Material 8 in B3.
- For Part 6, this would be Raw Material 1 in B1 only.

Hopefully that makes sense, any ideas gratefully received!

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, you will get many more potential helpers if you provide your sample data in a form that can be copied for testing, rather than an image like that. My signature block below has help on that.

For the layout shown on the 'Data' sheet (Adjust sheet name in my formula below), then in the 'Summary' sheet, try this formula, copied down.

Excel Workbook
	A	B
1	Part 2	Raw Material 1
2		Raw Material 6
3		Raw Material 8
4
5
Summary

 

[ah2931]

Welcome to the MrExcel board!

For the future, you will get many more potential helpers if you provide your sample data in a form that can be copied for testing, rather than an image like that. My signature block below has help on that.

For the layout shown on the 'Data' sheet (Adjust sheet name in my formula below), then in the 'Summary' sheet, try this formula, copied down.

Summary

	A	B
1	Part 2	Raw Material 1
2		Raw Material 6
3		Raw Material 8
4
5

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:114px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IFERROR(INDEX(Data!B$2:I$2,AGGREGATE(15,6,(COLUMN(Data!B$2:I$2)-COLUMN(Data!B$2)+1)/(INDEX(Data!B$3:I$12,MATCH(A$1,Data!A$3:A$12,0),0)<>0),ROWS(B$1:B1))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you very much for that Peter, absolutely ideal.

Yes, will keep in mind to use copyable data rather than an image for anything in future.

Thanks again!

 

[Peter_SSs]

Thank you very much for that Peter, absolutely ideal.

Yes, will keep in mind to use copyable data rather than an image for anything in future.

Thanks again!

Glad it worked for you. Thanks for letting us know.