I would like to combine information from 2 tables in just one?

G

Guest

I have two tables with warehouse information. the first tells me the code,
names and units of all the products I have (even if they are not in stock).
The second has movement information, from witch I need the batch number and
the quantity (with only the ones I have in stock).

I would like to merge them together getting a table with the Code; Name;
Unit; batch and quantity.

Thank you, for your help. Sorry about my English.
 
G

Guest

Hi,

I have assumed table 1 (in Sheet1) as below and we will ADD
Batch/Quantity to it.

Data starts in Row 2 column A

A B C D E
Code Name Unit Batch Quantity
A001 Part no 01 Unit 1 B001 10
A002 Part no 02 Unit 2 B002 20
A003 Part no 03 Unit 3
A004 Part no 04 Unit 4
A005 Part no 05 Unit 5 B005 50
A006 Part no 06 Unit 6 B006 60
A007 Part no 07 Unit 7 B007 70
A008 Part no 08 Unit 8

Table 2 (in Sheet2) is as below and I have named the range A2 to C7 as
"Stock"

Code Batch Quantity
A001 B001 10
A002 B002 20
A005 B005 50
A006 B006 60
A007 B007 70
A009 B009 90


In Table 1, column D, row 2 i.e. D2 put:

=IF(ISERROR(VLOOKUP($A2,Stock,2,0)),"",VLOOKUP($A2,Stock,2,0))

In E2 put:

=IF(ISERROR(VLOOKUP($A2,Stock,3,0)),"",VLOOKUP($A2,Stock,3,0))


If no match is found on Code, both Batch & Quantity will be blank.

Copy these formulae down in Table 1.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top