Find Numbers in a Bill of Material

[nick99_in]

Hi All,
The subject line does not do justice to what i am asking for, but here is my question.
I have a table in worksheet one.
Column A has list of components, starting from row # 2
From column B to D, in row 1 i have the parent part numbers these components go into.
Some components go into all the parent part numbers and some only in one.

Example of a pen:
Green Pen Red Pen Black Pen
Cap 1 1 1
Cartridge 1 1 1
Green Ink 1
Red Ink 1
Black Ink 1
Cover 1 1
Product Sticker 2 1

Here is what i want to do,
On sheet two, if i put the component number in a row, the column next to it should tell me which all parts does that componente go into and how many times.

Is there a way to do this?
Any help would be appreciated.
Nick

 

[starl]

It would help if you had provided an example of a correct answer. The copy/paste of data loses your table layout and you aren't reflecting numbers. Makes it a bit difficult to understand - so I'm guessing a bit here.
My first question: Why not just look at the data table and get your answer? Or do you want ALL the answers in a single column? If that's the case - you'll need a macro.
But, if you just want another way of looking at the data - a simple solution is to use a pivot table. Problem - it will still list the items where the use is zero for the components - But it does summarize in a thinner table.

<table x:str="" style="border-collapse: collapse; width: 350px; height: 137px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 98pt;" span="2" width="131"> <col style="width: 28pt;" width="38"> <tbody><tr style="height: 13.1pt;" height="17"> <td class="xl22" style="height: 13.1pt; width: 98pt;" width="131" height="17">Black Ink</td> <td class="xl22" style="width: 98pt;" width="131">Count of Green Pen</td> <td class="xl25" style="width: 28pt;" x:num="" width="38" align="right">0</td> </tr> <tr style="height: 13.1pt;" height="17"> <td class="xl23" style="height: 13.1pt;" height="17">
</td> <td class="xl24">Count of Red Pen</td> <td class="xl26" x:num="" align="right">0</td> </tr> <tr style="height: 13.1pt;" height="17"> <td class="xl23" style="height: 13.1pt;" height="17">
</td> <td class="xl24">Count of Black Pen</td> <td class="xl26" x:num="" align="right">1</td> </tr> <tr style="height: 13.1pt;" height="17"> <td class="xl22" style="height: 13.1pt;" height="17">Cap</td> <td class="xl22">Count of Green Pen</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 13.1pt;" height="17"> <td class="xl23" style="height: 13.1pt;" height="17">
</td> <td class="xl24">Count of Red Pen</td> <td class="xl26" x:num="" align="right">1</td> </tr> <tr style="height: 13.1pt;" height="17"> <td class="xl23" style="height: 13.1pt;" height="17">
</td> <td class="xl24">Count of Black Pen</td> <td class="xl26" x:num="" align="right">1</td> </tr> </tbody></table>

 

[nick99_in]

Starl,
this Bill of Material i am talking about is a very complex sheet.
I cannot use lookup since it is only applicable to look up one value across a table and give me one value in return. i am looking for a 'LOOP' option.
I have pictures of my example with me, but i dont know how to upload them. i am looking for an attachment option but cannot find it.
Can you help me out here?
Nick

 

[starl]

We don't allow attachments to be uploaded at the board.

I wasn't suggesting a lookup - but a pivot table.

Lookups are limiting - yet then can also be a bit complex. A lookup can be 2D - matching a row and column to return a value, as long as we can help it find the row and column.

 

[nick99_in]

I dont think lookups are going to work. I have tried them already. Makes the table too long and messy.

I think a search-loop function code in VB is the answer.
Unfortunately i dont have VB knowledge.