List all Possible Combinations in Excel

[Btibert]

I know there has been a similar question, but I was hoping that someon
could help me out. Here is my best description of my problem.

There are two sets of variables to my problem.

The first variable can either take a value of A or N, and are i
combinations of three. The values can be replaced, meanin
combinations can be AAA or AAN, ANN, etc.

The second variable can take the value of 1-9, but can not be replaced
and also comes in sets of threes.

The combinations I want to evaluate come in the form of (A1 A2 A3)o
(A1 N3 A8) etc, but not A1 A1 A5 etc because it violates the fact tha
once a number 1-9 is used (in this example 1), it can no longer b
another value in the set of three.

I know that since there is a logical value with a numerical value, i
may be difficult to list the combinations.

How can I calculate the number of combinations of the two variables a
well as list all of the possible combinations.

Any help will be greatly appreciated.

Thank you,

Broc

 

[Jerry W. Lewis]

If order is important, as it seems to be, given the second example of
(A1 N3 A8), then the number of combinations is
2^3 * 9*8*7

The following (untested) code should generate all the combinations

numbers = "123456789"
For i1 = 1 To 2
str1 = Mid("AN", i, 1)
For j1 = 1 To 9
num1 = Mid(numbers, j1, 1)
nums = IIf(j1 > 1, Mid(numbers, 1, j1 - 1), "") _
& IIf(j1 < 9, Mid(numbers, j1 + 1, 9 - j1), "")
For i2 = 1 To 2
str2 = Mid("AN", j, 1)
For j2 = 1 To 8
num1 = Mid(nums, j2, 1)
nums = IIf(j2 > 1, Mid(nums, 1, j2 - 1), "") _
& IIf(j2 < 8, Mid(nums, j2 + 1, 8 - j2), "")
For i3 = 1 To 2
str3 = Mid("AN", k, 1)
For j3 = 1 To 7
num1 = Mid(nums, j3, 1)
nums = IIf(j3 > 1, Mid(nums, 1, j3 - 1), "") _
& IIf(j3 < 7, Mid(nums, j3 + 1, 7 - j3), "")
Next j3
Next i3
Next j2
Next i2
Next j1
Next i1

Jerry

 

[Btibert]

I appreciate your prompt response and attempt to help. The orde
actually doesnt matter, which I presume complicates the matter eve
more.

For example, A1 N6 A4 is a possible combination as compared to only A
N4 A6.

Thanks for any help,

Broc

 

[Jerry W. Lewis]

I would count the cases as follows (which should give you some ideas for
modifying the VBA code to list them)

There are 2 ways to have 3 identical letters (AAA, BBB)
There are 2 ways to have 1 odd letter (AAB, ABB)

The order of the numbers is immaterial ammong the identical letters, so
if there is an odd letter you can choose its number first, thus there are

2*( COMBIN(9,3) + 9*COMBIN(8,2) ) = 672

unique combinations of letters and numbers.

Jerry