Danieljfern
New Member
- Joined
- Apr 10, 2013
- Messages
- 9
Hi All,
I originally posted a question here about Syllogisms (If A = B and B = C then A = C).
Here is the Spreadsheet I need help with.
!Please note that to reduce calculation time, I’ve removed all but a text version of most of the formulas, which can be found at the top of their respective columns!
Essentially, my company sells 40,000 different products, some are essentially the same and some links have been made manually between them, however I wanted to create a excel workbook that used the logic (If A = B and B = C then A = C) to automatically create more appropriate links.
I have created a solution but as there are so many products, and I have to be able to add new links manually when they are seen by customers or sales reps, it’s ended up being quite a large and cumbersome workbook.
I started off using our 12 digit alpha numeric product code, however the formulas were regularly giving results which were smashing the cell character limit in excel 2007. Therefore I created an index code for each product e.g. Product BHC123456789 became A, and Product BHC123456788 became B, and so on all the way to ZZZ This can be seen in column B of the Calculation sheet (I’ve removed the product codes as this is actually quite a valuable resource for us and gives us a competitive edge). It has worked for a while, however a few links have now been made which have ballooned the data, and so I cannot get past its current state as every time I calculate, excel runs out of resources. I’ve already disabled the undo feature in the registry and I’m using a ready boost dongle (probably not helping much) to increase memory but now I think I need some help.
Can anyone suggest how I can get this to run faster and use less resource?
A brief explanation of its function; (there’s so many formulas, I’m hoping you can see what I’m doing without me explaining each one, please ask if there’s a specific explanation needed)
Any new links that are made manually are checked and logged in the Additions Sheet, Products without a previous link are added in the Calculation sheet (column A) and given an index (column B). The additions Sheet then checks that the links haven’t already been made in the spreadsheet (column F) and I can remove any that have. Then I concatenate multiple links in the Additions Concat sheet. These are then checked in the calculation sheet (column G) and any products who have additions have their link list (column I, calculation sheet) overwritten with the relative addition concat (additions concat column R).
Then using the formula in Calculation Sheet L1, using Ctrl + Shift + Enter for an array formula, links are automatically made throughout the whole product list. This is the bit that’s causing me problems, here’s where it falls down every time (please note I’m only calculating a portion of these cells at a time, not the whole range).
Should I get through this part, I concatenate all the results for each product, then using text to columns, transposing the data, removing duplicates via the embeded macro, transpose back then concatenate again and overwrite column I on the calculation sheet.
Then I can repeat the Array formula and subsequent steps until no more links are made (the appearance count in column F, calculations sheet (using the formula in F1) is equal to the last count column (column E))
Sorry this is a real simplification of the process, so I hope it makes sense. Please help!
I originally posted a question here about Syllogisms (If A = B and B = C then A = C).
Here is the Spreadsheet I need help with.
!Please note that to reduce calculation time, I’ve removed all but a text version of most of the formulas, which can be found at the top of their respective columns!
Essentially, my company sells 40,000 different products, some are essentially the same and some links have been made manually between them, however I wanted to create a excel workbook that used the logic (If A = B and B = C then A = C) to automatically create more appropriate links.
I have created a solution but as there are so many products, and I have to be able to add new links manually when they are seen by customers or sales reps, it’s ended up being quite a large and cumbersome workbook.
I started off using our 12 digit alpha numeric product code, however the formulas were regularly giving results which were smashing the cell character limit in excel 2007. Therefore I created an index code for each product e.g. Product BHC123456789 became A, and Product BHC123456788 became B, and so on all the way to ZZZ This can be seen in column B of the Calculation sheet (I’ve removed the product codes as this is actually quite a valuable resource for us and gives us a competitive edge). It has worked for a while, however a few links have now been made which have ballooned the data, and so I cannot get past its current state as every time I calculate, excel runs out of resources. I’ve already disabled the undo feature in the registry and I’m using a ready boost dongle (probably not helping much) to increase memory but now I think I need some help.
Can anyone suggest how I can get this to run faster and use less resource?
A brief explanation of its function; (there’s so many formulas, I’m hoping you can see what I’m doing without me explaining each one, please ask if there’s a specific explanation needed)
Any new links that are made manually are checked and logged in the Additions Sheet, Products without a previous link are added in the Calculation sheet (column A) and given an index (column B). The additions Sheet then checks that the links haven’t already been made in the spreadsheet (column F) and I can remove any that have. Then I concatenate multiple links in the Additions Concat sheet. These are then checked in the calculation sheet (column G) and any products who have additions have their link list (column I, calculation sheet) overwritten with the relative addition concat (additions concat column R).
Then using the formula in Calculation Sheet L1, using Ctrl + Shift + Enter for an array formula, links are automatically made throughout the whole product list. This is the bit that’s causing me problems, here’s where it falls down every time (please note I’m only calculating a portion of these cells at a time, not the whole range).
Should I get through this part, I concatenate all the results for each product, then using text to columns, transposing the data, removing duplicates via the embeded macro, transpose back then concatenate again and overwrite column I on the calculation sheet.
Then I can repeat the Array formula and subsequent steps until no more links are made (the appearance count in column F, calculations sheet (using the formula in F1) is equal to the last count column (column E))
Sorry this is a real simplification of the process, so I hope it makes sense. Please help!
