Combining data from 2 worksheets

[Douglas6587]

I have worksheets in 2 separate workbooks. One contains a long list of individuals and their year-to-date production figure. Each individual is identified by a distinct number. My second sheet contains a smaller list of individuals and some additional production data which needs to be added to the first sheet. Those on the second sheet may or may not be on the first list. These individuals are also identified by a distinct number. If the individuals appear on each list, they will have the same distinct number on each list.

I currently have to go through the first list and search for each individual on the second list and manually key in the data from the second list in order to combine the numbers and calculate their total production.

Is there a way to merge or combine these lists so that I can calculate their total? Ideally I would like the data from the second sheet to appear in a new column in the row of the individual to which it belongs.

 

[agihcam]

Hi - Welcome to Mr. Excel Board.
It would be best if you could provide more info such as ranges, columns etc.

 

[JohnnyTightlips]

A simple VLOOKUP should solve the problem for you.

e.g. In the new column of workbook 1:
=VLOOKUP(A1,[otherworkbook.xls]Sheet1!$A$1:$B$5,2,False)

Where A1 is the unique number in Workbook 1, A1:B5 is the range in the other workbook, (2 assumes the data is in column 2:column B)

You may wish to remove the resultant errors you will ge for those IDs for which there is no match, in this case:

=if(iserror(VLOOKUP(A1,[otherworkbook.xls]Sheet1!$A$1:$B$5,2,False)),"",VLOOKUP(A1,[otherworkbook.xls]Sheet1!$A$1:$B$5,2,False))

 

[Douglas6587]

Thanks Johnny! The VLookup worked perfectly!! I wasn't sure if that was the function to use or not and now you have saved my hours of manual work!

Thanks for the help!