Sub abc()
Dim v(1 To 6) As Variant
Dim v1() As Long, inst() As Long
For i = 1 To 6
Set rng = Range(Cells(2, i), Cells(2, i).End(xlDown))
v(i) = Application.Transpose(rng.Value)
Next
cnt = 0
For i = LBound(v(1)) To UBound(v(1))
l1 = v(1)(i)
ReDim inst(1 To 6)
ReDim v1(1 To 85)
inst(1) = l1
v1(l1) = -1
For j = LBound(v(2)) To UBound(v(2))
l2 = v(2)(j)
If v1(l2) = 0 Then
v1(l2) = -1
inst(2) = l2
For k = LBound(v(3)) To UBound(v(3))
l3 = v(3)(k)
If v1(l3) = 0 Then
v1(l3) = -1
inst(3) = l3
For l = LBound(v(4)) To UBound(v(4))
l4 = v(4)(l)
If v1(l4) = 0 Then
v1(l4) = -1
inst(4) = l4
For m = LBound(v(5)) To UBound(v(5))
l5 = v(5)(m)
If v1(l5) = 0 Then
v1(l5) = -1
inst(5) = l5
For n = LBound(v(6)) To UBound(v(6))
l6 = v(6)(n)
If v1(l6) = 0 Then
v1(l6) = -1
inst(6) = l6
cnt = cnt + 1
' Cells(cnt + 10, 1).Resize(1, 6) = inst
v1(l6) = 0
End If
Next n
v1(l5) = 0
End If ' v1(l5) = 0
Next m
v1(l4) = 0
End If ' v1(l4) = 0
Next l
v1(l3) = 0
End If ' v1(l3) = 0
Next k
v1(l2) = 0
End If ' v1(l2) = 0
Next j
v1(l1) = 0
Next i
MsgBox cnt
End Sub

produced 62,717,388

In light testing, that seemed to produce the correct results. Hopefully I
haven't missed something. I am thinking it could be culled down to much
less code and more arrays, but enough for now if it works.

--
Regards,
Tom Ogilvy

" wrote:

Hi!

I was wondering if it is ever possible to make combinations in excel or
access or any other software based on my criteria.

Total number I have starts from 1 to 85. I want to count and list all
possible combinations but in a specific way. For instance 1,2,3,4,5,6
is a valid combination but for me it is NOT the first number in a
combination (in this example - 1) should be present in N1 column (It
is), the second number and third number (2 and 3) are also a part of N2
and N3 but the next three numbers (4 5 and 6) are not a part of N4 N5
and N6 hence it is INVALID.

I know that all possible combinations of 6 numbers of total 85 would be
437353560
[ =COMBIN(85,6) ]

Is it possible to count how many out of 437353560 falls within my
criteria and preferably list them? Nobody can do it manually, I was
wondering if it is possible to do it using a computer program.

N1 N2 N3 N4 N5 N6
1 2 3 52 58 61
2 3 4 53 59 62
3 4 5 54 60 63
4 5 6 55 61 64
5 6 7 56 62 65
6 7 8 57 63 66
7 8 9 58 64 67
8 9 10 59 65 68
9 10 11 60 66 69
10 11 12 61 67 70
11 12 13 62 68 71
12 13 14 63 69 72
13 14 15 64 70 73
15 16 65 71 74
16 17 66 72 75
17 18 67 73 76
18 19 68 74 77
19 20 69 75 78
20 21 70 76 79
22 71 77 80
23 72 78
24 73 79
25 74
26 75
27 76
28 77
29 78

Sandy