Transferring data from one workbook to another, if specific attribute matches

[khelza]

I don't have a very extensive background with excel, and no background with macros, but I have the feeling I will need to use a Macro to carry out the task in question..

I have two workbooks; one that serves as a database for all the materials my company ever uses, and another that servers as an individual Bill of Materials (BOM) for each project.

The database is complete, with columns for partID, name, manufacturer, part#, etc. It also has every part occurring only once, as it should. Therefor, each part# should only occur once.

The BOM, however, has many instances where parts are repeated because the same part can be used in many different sections of the project.
The BOM does have a column for partID, but it has not yet been filled in, and I am trying to avoid filling it in manually, as there are 1000s of parts listed.
It also has columns for name, manufacturer, part#, etc, all of which are already populated. The name could be a long description, and the manufacturer can be the same across many parts, so to me, the part# column is the useful identifier between the two workbooks.

So basically, I am assuming that I will need to write some sort of macro procedure that will copy partID from the Database.xlsx to BOM.xlsx if column "part#" is the same between the two workbooks. Is this correct to assume?

Any tips or references of where to start? Anything would be helpful!

Thanks!

 

[gaz_chops]

Try in BOM sheet under partID
=index(database sheet!column with partID in,(match(1st cell with part# in,database sheet! column with part# in,0))

(like =INDEX(database!$H$2:$H$5000,MATCH($B1,database!$G$2:$G$5000,0))

 

[gaz_chops]

Realised it is dif workbooks so will look more like

=INDEX([Database.xlsx]sheet1!$H$2:$H$5000,MATCH($B1,[Database.xlsx]sheet1!$G$2:$G$5000,0))

 

[khelza]

That worked! Thanks so much!

 

[gaz_chops]

You're welcome.

 

[khelza]

Can this same formula be extended to fill in more than just one column?

I have tweaked your formula to produce the following function:

In BOMSheet!Manufacturers.Cell=(Copy QuotesSheet!Manufactures.Column If MATCH(BOMSheet!Part_ID.Cell with QuotesSheet!Part_ID.Column)

[COLOR=#333333]=INDEX(quote!$C$2:$C$377,MATCH($L13,quote!$A$2:$A$377,0))[/COLOR]

I would like the formula to not only copy
Quotes!Manufacturers.Column into BOM!Manufacturers.Column
But also:
Quotes!Model_Number.Column into BOM!Model_Number.Column
Quotes!Pri_Vendor.Column into BOM!Pri_Vendor.Column
Quotes!Alt_Vendor.Column into BOM!Alt_Vendor.Column
Quotes!Cost_CND.Column into BOM!Cost_CND.Column

Is this possible with the current formula?

 

[gaz_chops]

As a formula, i would extend the index range to cover all the data, then add the col header as a lookup
=INDEX(quote!$C$2:$LC$377,MATCH($L13,quote!$A$2:$A$377,0),match(Col Header,quote!$A$2:$L$2,0))

 

[khelza]

Would this formula still reside in the manufacturers cell?

For match(Col Header,quote!$A$2:$L$2,0))
Is there a way to call All headers? Or did you mean to do it individually?

 

[khelza]

=INDEX(quote!$C$2:$G$377,MATCH([Part_ID],quote!$A$2:$A$377,0),MATCH(Table1[[#Headers],[Manufacturer]:[Cost_CND]],quote!C1:G1, 0))

Doesn't seem to be working for me..

 

[gaz_chops]

Sorry, 1 header at a time, but if you have the same headers on both sheets, you can reference the cell with the header, easier if multiple columns.
e.g.
=INDEX(quote!$C$2:$G$377,MATCH([Part_ID],quote!$A$2:$A$377,0),MATCH(Table1c$1,quote!C1:G1, 0))