Feb 27, 2012

I have 5 arrays, each containing 5 elements and want to determine all the combinations.

1 2 4 8 16

1 2 4 8 16

1 2 4 8 16

1 2 4 8 16

1 2 4 8 16

So, for example, we would start with the first element of the first line and work our way down through each subsequent line on the first column and wind up with combinations as follows:

1, 1, 1, 1, 1

2, 1, 1, 1, 1

4, 1, 1, 1, 1

8, 1, 1, 1, 1

16, 1, 1, 1, 1

Then, go down the second column starting with elements from the first array row:

1, 2, 1, 1, 1

2, 2, 1, 1, 1

4, 2, 1, 1, 1

8, 2, 1, 1, 1

16, 2, 1, 1, 1

In all, there will be 5^5 = 3125 possible combinations.

In addition, some elements in each of the 5 array rows may have a value of 0 instead of what is shown above. So, for example, the rows may appear as:

1, 2, 4, 0, 16

0, 2, 0, 8, 0

1, 0, 4, 8, 0

0, 2, 0, 0, 0

0, 0, 0, 8, 0

So, in essense, the first element in each array row may be either a 0 or 1, the second either a 0 or 2, the third either a 0 or 4, the fourth either a 0 or 8 and the fifth either a 0 or 16. If a zero appears in any of the combinations, I do not want the 0 entry to the list of combinations. So, for example, if a combination gives (from the first column above):

1, 0, 1, 0, 0, then I do not want to include the 0 as one of the combination elements and we would have as some of the possible combinations:

1, 2, 1, 2, 8

2, 2, 1, 2, 8

16, 8, 8, 2, 8....etc., but NOT 16, 8 , 8, 2, 0

either a VBA code or a way to hard-code in a worksheet with how this can be accomplished.

View 9 Replies
View Related