jmenche
02-14-2011, 07:53 AM
Howdy,
I am looking for a little help in making the attached spreadsheet more efficient. What the spreadsheet does is perform calculations on each unique combination of products based on the sheet name. For example, the 'Results3' contains every unique combination of 3 products (field names) from the Data tab. You'll see that I had to create a separate procedure for each combination. The only difference is an additional layer of nesting. Does anyone know a way to make this more dynamic?
Another pitfall of this kind of analysis is memory. Depending on how big the data set is, the number of permutations can reach into the millions. I do not work with arrays much but I have them in there and dimmed them with a huge number of rows. Is there a better way to handle the memory?
Thanks for any help
Jeff
I am looking for a little help in making the attached spreadsheet more efficient. What the spreadsheet does is perform calculations on each unique combination of products based on the sheet name. For example, the 'Results3' contains every unique combination of 3 products (field names) from the Data tab. You'll see that I had to create a separate procedure for each combination. The only difference is an additional layer of nesting. Does anyone know a way to make this more dynamic?
Another pitfall of this kind of analysis is memory. Depending on how big the data set is, the number of permutations can reach into the millions. I do not work with arrays much but I have them in there and dimmed them with a huge number of rows. Is there a better way to handle the memory?
Thanks for any help
Jeff