Consolidating data from different workbooks

[Guest]

I have 3 different workbooks. They all have part numbers in one column and
qty in another. I need to find part numbers from one worksheet that are
imbedded in the other 2. Then I need to total the qty for each part number.
Any help is appreciated.

 

[JulieD]

Hi

you can use the VLOOKUP function to lookup the part numbers in the different
books and return the values to your first workbook for summing.

E.g.
with Book1 open and the part number you're looking for in column A and the
quantity in that book in column B, in column C use a formula such as
=IF(ISNA(VLOOKUP(A2,'[book2.xls]Sheet1'!$A$2:$B$100,2,0)),0,VLOOKUP(A2,'[book2.xls]Sheet1'!$A$2:$B$100,2,0))
which says look for the value in A2 in Sheet1 of Book2 (column A) and return
the related quantity from column B where there is an exact match - otherwise
return 0.

in column D do the same for the other workbook now in column E you can do a
simple =SUM(B22) formula - these formulas can then be copied down for all
your records.

Cheers
JulieD
PS alternatively you could look at using Pivot Tables, taking data from
multiple consolidation ranges.
check out http://www.contextures.com/xlPivot08.html for details.