I am developing customizable products where the same parts can sometimes build several projects. Kind of like the same set of Lego pieces will build item #1, item #2, or item #3.
I'm looking for spreadsheet wizardry or software that can tell me that
- The parts that will build item #1 will also build item #2, or item #3. Let's call this the Base Kit.
- I'll also be able to build items #4 and #5 with the Base Kit if I add 2 of part A and 4 of part B.
- I'll also be able to build items #6, #7, and #8 with the Base Kit if I add 4 of part C and 6 of Part D.
- With both sets of add-ons, the Base Kit only builds 1 additional project... or zero... or 5 more.
The parts calculators I found were designed to calculate the parts list for just one project.
The recipe calculators I checked didn't have the needed functionality.
Now I'm hunting around for other options.
I would prefer a way to do what I want using an Excel (or Open Office) spreadsheet, since I already price parts in Excel. Next best would be software that runs on a Mac, iPad, or the web. Software cost should be under $100, preferably under $30. If over $5, I want software with a trial period of at least 5 days.
My original idea was to make a parts list of each of the 80 projects in Excel -- something like this:
Part # item 1 uses item 2 uses stick A 3 6 stick B 8 12 bolt 24 36 nut 24 36
The parts for project #2 will build project #1, but the parts for project #1 won't build project #2.
If I then add some kind of compare function (which I don't know how to do), I would end up with a list like this:
Parts from ...will also build item #1 item #2 item #1 item #3 item #1, item #2 item #4 item #1 item #5 item #6 item #1, item #2, item #3 item #7 item #5, item #14, item #23
I then look for items that have several items in the "will also build" column, and use those as the basis for kits.
Here projects #3, #6, and #7 look like good candidates for kits.
Then I can search for what projects also have project #6 in the "will also build" column to find candidates for slightly extending the kit content.