Two spreadsheets, one is 6000 long the other 800. I need to find the 800 in the 6000

[ELIVERGARA]

I have a spreadsheet of 6000+ inventory parts (inventory.xlsx). I have one section of the warehouse on another spreadsheet (warehouse4.xlsx).

My boss wants to know the cost of this section in particular, so he wants me to find these 800 parts in the 6000 list, and copy the quantity on hand and unit cost from the long list onto the short list so we can sum only that.

My long list (inventory.xlsx) is like this:
A...................B.........................C...............D
Part# | Part Description | Qty On Hand | Unit Cost

My short list is only Part# on Column A (warehouse4.xlsx).

I tried to run some VBA to copy the value on warehouse4.xlsx (A2), find it in inventory.xlsx, select the cell, move (offset) and copy the values from qty on hand and unit cost into warehouse4.xlsx and then loop this, but I cannot get it to work. If I set a variable to store the copied value as string, it returns error 91, if I set it as integer it works, but finds something completely different since it uses the value rather than the string.

Any help? Thanks!

[mehmetcik]

You are over complicating this.

Book 1 has a list of numbers with the text equivalent

Book2 has a list of numbers and a formula to find the numbers in book1 and get the text equivalent.

[ELIVERGARA]

Thank you very much... but... I swear I cannot get it to work. It looks so simple, I actually have it working from your files, but when I try to replicate with my data I get an N/A error.
Would you be so kind as to look at these and tell me where I am not doing it right? I basically need to copy the value on Column B and Column H to On Hand and Unit Cost respectively.

Thanks again!

[mehmetcik]

Try this formula in B2 and fill down

Please Login or Register  to view this content.

Try this inC2 and fill down

Please Login or Register  to view this content.