Creating combinations based on a criteria

[Maxi]

----------------------------------------------
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
----------------------------------------------
| 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
----------------------------------------------
| 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 |
----------------------------------------------
| 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
----------------------------------------------
| 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 |
----------------------------------------------
| 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 |
----------------------------------------------
| 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 |
----------------------------------------------
| 64 | 65 | 66 | 67 | 68 | 69 | 70 | 71 | 72 |
----------------------------------------------
| 73 | 74 | 75 | 76 | 77 | 78 | 79 | 80 | |
----------------------------------------------

Please paste the above data in notepad with COURIER NEW font to view it
clearly.

I have a GRID in Excel like the one shown above. If you look at a
combination of 9 numbers " 9 17 25 33 41 49 57 65 73" You will notice
that these numbers do not intercept horizontally or vertically with any
other number within the combination.

Easier clarification: Take the first number in the combination "9" and
see vertically downwards (9th column), you would notice that any other
number from that COLUMN is not present in the combination and if you
see towards left (1st row), you would notice that any other number from
that ROW is not present in the combination

I would like to know how many such combinations of 9 could be made with
the above criteria.

Other examples for a combination of 9 numbers
2 10 21 32 40 51 61 72 80
8 9 16 33 41 49 57 65 73

I found a macro written by Myrna Larson that creates combinations. Here
is a link
http://www.mrexcel.com/board2/viewtopic.php?t=179001

Can this macro be modified to suit my needs?

 

[Tom Ogilvy]

If your numbers went up to 81,
There are 9 different ways you could select the first column, 8 ways to
select the second, 7 ways to select the 3rd

so the answer would be 9! = 362,880 in my estimation. While only having 80
numbers to choose from would lower that amount, it would still be sizable.
What would be the utility of generating and listing the possibilities?

I don't see a direct roll for Myrna's code it is primarily designed to
generate all combinations of 9 numbers from 80 - this wouldn't be feasible.
(around 232 Billion possibilities I think)

 

[Maxi]

Actually this is my friend's question and I proudly told my friend that
I can do it but when I actually sat to do this I am not sure what logic
to use to write a macro. Thats when I found the macro (link posted
above) and I tried to modify that but not able to do it.

Do you think a macro can be written to list those 362880 possible
combinations? Can you help me with that?

 

[Tom Ogilvy]

Sub ABC()
Dim rng As Range, t As Variant
Dim v As Variant, v1 As Variant
Dim v2 As Variant, v3 As Variant
Dim v4 As Variant, v5 As Variant
Dim v6 As Variant, v7 As Variant
Dim v8 As Variant, v9 As Variant
Dim i As Long, j As Long, k As Long
Dim l As Long, m As Long, n As Long
Dim o As Long, p As Long, q As Long
Dim cnt As Long, wrt As Long
Dim rw As Long, col As Long
Dim start As Single
start = Timer
wrt = 1
cnt = 0
col = 1
rw = 1
Set rng = Worksheets(1).Range("A1").CurrentRegion
t = rng.Value
If wrt = 1 Then _
Worksheets.Add After:=Worksheets(Worksheets.Count)
If rng.Columns.Count <> 9 Then Exit Sub
ReDim v(1 To 9)
For i = 1 To 9
v(i) = i
Next
' row 1
For i = 1 To 9
v1 = v
v1(i) = emtpy
' row 2
For j = 1 To 9
v2 = v1
If v2(j) <> Empty Then
v2(j) = Empty
' row 3
For k = 1 To 9
v3 = v2
If v3(k) <> Empty Then
v3(k) = Empty
' row 4
For l = 1 To 9
v4 = v3
If v4(l) <> Empty Then
v4(l) = Empty
' row 5
For m = 1 To 9
v5 = v4
If v5(m) <> Empty Then
v5(m) = Empty
' row 6
For n = 1 To 9
v6 = v5
If v6(n) <> Empty Then
v6(n) = Empty
' row 7
For o = 1 To 9
v7 = v6
If v7(o) <> Empty Then
v7(o) = Empty
' Row 8
For p = 1 To 9
v8 = v7
If v8(p) <> Empty Then
v8(p) = Empty
' Row 9
For q = 1 To 8
v9 = v8
If v9(q) <> Empty Then
cnt = cnt + 1
If cnt Mod 1000 = 0 Then _
Debug.Print cnt, i, j, k, l, m, n, o, p, q
If wrt = 1 Then
Cells(rw, col + 0) = t(1, i)
Cells(rw, col + 1) = t(2, j)
Cells(rw, col + 2) = t(3, k)
Cells(rw, col + 3) = t(4, l)
Cells(rw, col + 4) = t(5, m)
Cells(rw, col + 5) = t(6, n)
Cells(rw, col + 6) = t(7, o)
Cells(rw, col + 7) = t(8, p)
Cells(rw, col + 8) = t(9, q)
End If
rw = rw + 1
If rw > 65536 Then
col = col + 10
rw = 1
End If
End If
Next q
End If
Next p
End If
Next o
End If
Next n
End If
Next m
End If
Next l
End If
Next k
End If
Next j
Next i
Debug.Print cnt, rw, col
Debug.Print Format(Timer - start, "#,##0.00")
End Sub

 

[Tom Ogilvy]

Your table of numbers (1 to 80) must be on the first sheet in the tab order
beginning in A1 for 9 columns and 9 rows.

 

[Maxi]

Super !

I sat with my friend to understand the code coz I am not a pro in
coding. Basically, I need to understand the mathematics behind this. I
understood your formula of 9! The formula works because rows and
columns count were same (9).

Lets say, if there were 10 columns and 8 rows and if I wanted to check
how many combinations of 8 numbers are possible, then what would be the
formula to check the count? Just curious...

will it be 8! or 10! ?

Example data

_1 _2 _3 _4 _5 _6 _7 _8 _9 10
11 12 13 14 15 16 17 18 19 20
21 22 23 24 25 26 27 28 29 30
31 32 33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60
61 62 63 64 65 66 67 68 69 70
71 72 73 74 75 76 77 78 79 80

 

[Tom Ogilvy]

10*9*8*7*6*5*4*3

on the first row, you have 10 choices. On the second row, you have 9
choices (you can't use the column from the first row) and so forth.