Multiple values returned based on two conditions in a matrix

ah2931

New Member
Joined
Mar 22, 2018
Messages
2
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:

Image1.jpg


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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
AB
1Part 2Raw Material 1
2Raw Material 6
3Raw Material 8
4
5
Summary
 

ah2931

New Member
Joined
Mar 22, 2018
Messages
2
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


AB
1Part 2Raw 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
CellFormula
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top