Help streamlining Alternatives (Syllogism) Matrix.

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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top