Macro to match and populate field

[ounvme]

I am looking to read a column on a worksheet check to see if any of those strings match the values in another column. If they do I want to copy a price over. I am using this to compare 2 BOM's with part costs and I would like to have the prices copy over so I do not have to look each one up if it was already priced in an existing BOM.

Columns are as follows

QTY-PART NUMBER-DESCRIPTION-COST

I have 2 workbooks. OLD BOARD and NEW BOARD

I want to look at the part numbers in NEW see if they exist in OLD and if they do copy the cost value from OLD to NEW.

I think I am explaining it right.

[kbkumar]

Hi - Assuming your data in OLD BOARD is in columns A and B, use the below in NEW BOARD sheet cell B2 and below:

Please Login or Register  to view this content.

If this is not helpful, please upload a sample workbook...

Thanks.

[ounvme]

Here are 2 sample sheets. Hope this helps.

I also need the data retained if the OLD_BOM is deleted.

[kbkumar]

Try this in E2 and below cells:

Please Login or Register  to view this content.

Part Number S20500-17 will show NA because this is not there in in OLD_BOM workbook.

Regarding your second point of retaining data when OLD_BOM is deleted, you can achieve this by pasting column E as values (just copy entire column and Press ALT + E + S + V Enter) or just break links (Press ALT + E + K, select Break Links). This will leave the value of the formula results as is and will stay even if you delete / move OLD_BOM workbook.

Hope this helps.