Following is the scenario: I have about 70 workbooks with monthly production data for about 700 products that I have to consolidate into one workbook for data analysis purposes. Since these workbooks have standard templates, I have to pull the same columns from each of these workbooks. I have already done this and all the data columns are placed side by side in the destination workbook. The issue is that over time some products are added or deleted in the different workbooks and therefore the rows for the items are not aligned.
So in the destination worksheet, the columns look something like this:
Column A: Item numbers from report 1
Column B: July orders for items in column A
Column C: Item numbers from report 2
Column D: August orders for items in column C
Column E: Item numbers from report 3
Column F: September orders for items in column E
But the issue is that, for example: Item #8007 maybe on row 4 in column A, whereas in column C it maybe on row 7.
I would like to compare the different monthly data for the products. As I am only interested in the products shown in the latest report, what I did was create a "sheet2" where column A had the most updated list of items. Next I created a macro that would pull hte order data from columns B, D, and F by vlookup for the appropriate items from sheet1. Although this worked great, with the sheer amount of data (370 columns and 700 row), the macros took about two hours to compute!
While my technique is working, I would like to know if anyone can suggest a more robust and efficient way to do this. Please let me know if you need any clarifications. Thanks!
So in the destination worksheet, the columns look something like this:
Column A: Item numbers from report 1
Column B: July orders for items in column A
Column C: Item numbers from report 2
Column D: August orders for items in column C
Column E: Item numbers from report 3
Column F: September orders for items in column E
But the issue is that, for example: Item #8007 maybe on row 4 in column A, whereas in column C it maybe on row 7.
I would like to compare the different monthly data for the products. As I am only interested in the products shown in the latest report, what I did was create a "sheet2" where column A had the most updated list of items. Next I created a macro that would pull hte order data from columns B, D, and F by vlookup for the appropriate items from sheet1. Although this worked great, with the sheer amount of data (370 columns and 700 row), the macros took about two hours to compute!
While my technique is working, I would like to know if anyone can suggest a more robust and efficient way to do this. Please let me know if you need any clarifications. Thanks!
