Need Help with Excel- Permutation Set

[importdistributors]

Tires color interior rims lights hp radio
1 300 300 0 3000 300 300 1000
2 1200 700 300 4000 1200 700 2500
3 2400 1200 1600 4000 2400 1200 3000
4 4500 3500 3500 4000 4500 3500 4500
5 5000 12000 12000 4500 5000 12000 n/a
6 10300 12300 7800 10800 8100 8100 n/a
7 11200 12700 8100 11800 9000 8500 n/a
8 12400 13200 9400 11800 10200 9000 n/a
9 14500 15500 11300 11800 n/a n/a n/a
10 15000 n/a 19800 12300 n/a n/a n/a


Above is an example of a chart with different prices for options to design a car. I need to select one from each column. I need every possibility to build the car. The N/A's mean that there is less than 10 options.

How can I design a Macro to give me every possible permutation?

There is a little more to the question, but this is the only part I need help with.

Your help is greatly appreciated.

Thanks.

 

[importdistributors]

No help at all :-( Thanks for anyone who tried.

 

[degibson]

Originally posted by: importdistributors
No help at all :-( Thanks for anyone who tried.

I started a solution, but I realized that excel doesn't have the horsepower to calculate a problem of your size with my solution. Anyway, here's the solution, for what its worth.

You can find my XLS example here.

The first step is to count all possible combinations. I'm going to do this example:
col1 col2 col3 col4
1 10 100 1000
2 20 200 2000
3 30
4 40

There are 64 possible combinations here. (4 x 4 x 2 x 2) In the case above with tires, etc., I count 10 x 9 x 10 x 10 x 8 x 8 x 4 = 2,304,000 combinations.

Start by inputting your combination matrix.

Next, below that, make column A a list of all numbers between 1 and the number of possible combinations, inclusive. In my case, this is A7 = 1, A8 = 2, ... A70 = 64. For the specific case above, excel will crap out because you need more rows. You could conceivably split the problem above into four parts.

OK, now we're going to define the first element (row 7) of column B as:
=INDEX($B$2:$B$5,MOD($A7-1,4)+1)

Now, you might ask, WTF is THAT?
Well...
- $B$2:$B$5 is the static column meaning the first independent variable.
- $A7 is the combination number
- 4 is the number of elements in the first column

Copy-and-paste that for all elements of B. B7:B70 for me.

Next, define the first element (row 7) of column C as:
=INDEX($C$2:$C$5,MOD(($A7-1)/4,4)+1)

This is a little different, and approaching the general form...
- $C$2:$C$5 is the static column meaning the second independent variable
- $A7 is the combination number
- The first 4 (under the /) is the product of the lengths of all previous independent variables.
- The second 4 (after the ,) is the length of the second (current) independent variable.

Again, copy-and-paste to all rows.

Similarly, for columns D and E:
=INDEX($D$2:$D$3,MOD(($A7-1)/16,2)+1)
=INDEX($E$2:$E$3,MOD(($A7-1)/32,2)+1)

Now, you can make column F the sum of columns B through E, and voila! you have all possible combinations.

EDIT: I should mention that I am not an Excel guru by any means... so there probably is a better way to do this.

 

[armstrda]

So you want a table with every possibilty outputted at the same time? or you want the ability to select options and have a price generated?

 

[esun]

As degibson pointed out, Excel isn't the appropriate tool for this job. A spreadsheet with 2 million rows will not work so well. I'd tackle it in a more traditional programming language, or perhaps something like MATLAB or Octave.

 

[KLin]

Can you post the rest of the details? Also, is this a homework assignment?