Consolidating data from different workbooks
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. |
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(B2:D2) 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. "deadsxy692003" wrote in message ... 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. |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com