Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
----------------------------------------------
| 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
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) -- Regards, Tom Ogilvy "Maxi" wrote in message oups.com... ---------------------------------------------- | 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
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 -- Regards, Tom Ogilvy "Maxi" wrote in message oups.com... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
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. -- Regards, Tom Ogilvy "Maxi" wrote in message oups.com... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating combinations based on a criteria
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. -- Regards, Tom Ogilvy "Maxi" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CREATING FORMULA IN ONE WORKSHEET BASED ON MULTIPLE CRITERIA IN AN | Excel Worksheet Functions | |||
Creating combinations | Excel Discussion (Misc queries) | |||
Creating function to insert data based on criteria | Excel Worksheet Functions | |||
Creating Combinations | Excel Worksheet Functions | |||
[VBA] Creating sheets based on criteria | Excel Programming |