Hello,
First of all thanks for all the help you provide. I've been finding solutions here for years to become more productive with excel.
I'm currently facing a task where I have to complete some inventory data (adding some classification to item e.g. 'non-alcoholic beverages' to coke, pepsi, water, etc.) before starting my analysis. I receive the data in several excels (5-6 workbooks), totaling around 200.000 rows of info and I have to cross-reference the items with a ~500.000 database to obtain the classification (placed on another excel workbook).
I'm currently using Index/Match formulas limited to cells with data in both workbooks (exact match, data cannot be ordered as far as I understand). Once the index/match finishes on each workbook, I paste as values and copy onto a new workbook (which will become the aggregated and processed data, starting point for the analysis. I perform the match on a "description" column which contains ~50 characters.
The issue is that this process is really slow, crashes excel often and it can take me days to aggregate the data. Each of the excels has different structure and it can change monthly so programming a solution would not be ideal as it would need frecuent adaptation.
Any ideas on how to speed up the process?
Thanks!
First of all thanks for all the help you provide. I've been finding solutions here for years to become more productive with excel.
I'm currently facing a task where I have to complete some inventory data (adding some classification to item e.g. 'non-alcoholic beverages' to coke, pepsi, water, etc.) before starting my analysis. I receive the data in several excels (5-6 workbooks), totaling around 200.000 rows of info and I have to cross-reference the items with a ~500.000 database to obtain the classification (placed on another excel workbook).
I'm currently using Index/Match formulas limited to cells with data in both workbooks (exact match, data cannot be ordered as far as I understand). Once the index/match finishes on each workbook, I paste as values and copy onto a new workbook (which will become the aggregated and processed data, starting point for the analysis. I perform the match on a "description" column which contains ~50 characters.
The issue is that this process is really slow, crashes excel often and it can take me days to aggregate the data. Each of the excels has different structure and it can change monthly so programming a solution would not be ideal as it would need frecuent adaptation.
Any ideas on how to speed up the process?
Thanks!
