Finding a value in column, another in row, and then placing

[Claudette]

I have a worksheet with a list of parts.  the first column contains
the Part Number.   The second column contains one of several components. 
The third column contains the quantity of that component that goes into that
part.<font face="Courier New, Courier, monospace">Part Number---Component---QtyofComponent

HL-45S--------M67O-----------3

HL-45S--------SSS------------2

HL-45S--------LABRXX---------10

CVA42---------M67O-----------4

CVA42---------SSS------------4

CVA42---------LABRXX---------10

CVA42---------SOAP-----------1</font>





Then I have a worksheet which lists the part number in column A, and has
each possible component as a column heading in columns J, K, L, and M.  
There are four possible components.  Not all parts will contain all
components.  The components for each part sometimes are listed in different
order.



Here's the second workbook and what it should end up looking like:<table cellpadding="2" cellspacing="2" border="1" width="50%"><tbody><tr><td valign="Top">Part Number</td><td valign="Top">M67O</td><td valign="Top">SSS</td><td valign="Top">LABRXX</td><td valign="Top">SOAP</td></tr><tr><td valign="Top">CVA42</td><td valign="Top">4</td><td valign="Top">4</td><td valign="Top">10</td><td valign="Top">1</td></tr><tr><td valign="Top">HL-45</td><td valign="Top">3</td><td valign="Top">2</td><td valign="Top">10</td><td valign="Top"></td></tr></tbody></table>




How can I make a macro that

1. Grabs the Part# and component from WrkBk1, then looks for the row in WrkBk2
that contains the same part number

2.  Then looks for the column in WrkBk2 that contains the component
name as the heading and fills in teh quantity in the intersection of the
component column and the partnumber row.



Help!!!!!!!!!!!!!!!!!



I have many ideas...such as creating variables of each of the two values
i'm searching for, then when I find them, recording the row location of the
first one, then the column location of the second one as another two variables,
then telling it to go to cell(var3,var4) blah blah.  I also thought
I could line all of the Partnumbers up by component, and simply paste all
the values for that component at once, then doing a separate run for the
next component, etc, thereby reducing the variables and the general confusion
of it all.



Thanks so much everyone!
This message was edited by Claudette on 2002-03-08 07:04

 

[Barrie Davidson]

Claudette, would a pivot table work meet your requirements?

 

[Claudette]

I don't know, I've never used a pivot table? what is it?

 

[Barrie Davidson]

On 2002-03-08 07:37, Claudette wrote:
I don't know, I've never used a pivot table? what is it?

For an introduction to pivot tables, have a look at:

http://ca.geocities.com/b_davidso/Web_Page_Files/Excel/pivottable1.html

Let me know if you need any further information.

Regards,

 

[Mark W.]

On 2002-03-08 07:37, Claudette wrote:
I don't know, I've never used a pivot table? what is it?

Also, see the Excel Help topic for "PivotTables: Analyzing data interactively". The table component labels are linked to a descriptive popup text box.