DOR,

This works brilliantly. Many thanks.

LAdekoya

"DOR" wrote:

If you will forgive a slight deviation from the way you specified the
problem in your first request, the following procedure will generate
all combinations of the 3 values, 0,1 and 2, in four positions:

In A1, B1, C1 and D1 enter the value 2

In A2, B2, C2 and D2 enter the value 0 (zero)

In A3: =IF(AND(B3=0,C3=0,D3=0),IF(A2<A$1,A2+1,0),A2)
In B3: =IF(AND(C3=0,D3=0),IF(B2<B$1,B2+1,0),B2)
In C3: =IF(D3=0,IF(C2<C$1,C2+1,0),C2)
In D3: =IF(D2=$D$1,0,D2+1)

Now drag/copy down as far as row 82. This will give you the 81
(3*3*3*3) different combinations of 0, 1, and 2 in 4 positions. You
can now use these values (+1 of course) as indexes into a range
containing your 3 permitted values for each position.

The reason for row 1 in my solution is to generalize the solution. Row
1 contains the maximum values that can occur in each position; these
values may differ one from the other. In your case they are are all 2,
representing the values 0, 1, and 2. If you had larger values you
would simply have dragged the formulas down further.

This could easily be modified to show combinations of 1, 2, and 3, but
I already had this from a prior question and chose not to change it. I
hope you don't mind.

HTH