The problem is solved. However I have few doubts
Summary of the problem
======================
I got hold of a vba code by Myrna Larson (July 25, 2000,
Microsoft.Public.Excel.Misc) which is used to list permutation and
combinations. It requires two 3 things:
1. What do you want to list (Permutations/combinations) Enter "C" or
"P" in cell A1
2. How many numbers do you want in a combination. Enter this in cell A2
3. List numbers vertically from cell A3 of which you need to list
combinations.
I have modified this vba code and added two more criteria:
1. How many matches. Enter in cell B1
2. What frequency? Enter in cell C1
Please download this excel file to see how it runs.
http://www40.brinkster.com/Maxlott/try.htm
In this example, I have 17 draws listed in range F1:Y17. Following is
the criteria I have used
A1 = C (I want to create combinations)
A2 = 3 (I want to create combinations of 3 numbers each)
B1 = 3 (I want to match all 3 numbers)
C1 = 6 (List combinations only if all the 3 numbers (matches) in a
combination apprears in more than or equal to 6 draws.
If you run the macro (DoIt) it will list only 42 combiations out of
19380 (=COMBIN(20,3)*17). These 42 combinations comply to the above
criterias given. If you check any combinations, you will see that all
three numbers matches in more than or equal to 6 draws. (Only thing
which is pending in this code is that it also lists duplicates which I
will remove later)
What I want more : Optimization
===============================
I have observed that on my computer (Intel Celeron 800 MHz 256MB SDRAM)
the total time it takes is 14:52 minutes to complete the code with the
criteria (Combinations A2=3, Matches B1=3 and frequency C1=6). If I
remove the conditional formatting in the range AC11:AL11 the total time
reduces to 12:45. If I remove the progress counter from the cell AI9
(by commenting the line combins = combins + 1 and Range("AI9").Value =
Format(combins / Range("AI7").Value, "00.00%")) then the time reduces
to 12:14. If I set the screen updating to FALSE, the total time reduces
considerably to 2:35 which is great.
Now I want you or somebody else to check my modified code to see if the
total time can be reduced more. I want this becuase when I want to
create combinations of 10 numbers, it should not consume unnecessary
time.
I have commented all lines prefexing it with '**
For i = 1 To UBound(ItemsChosen)
sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
Range("AC1").Offset(0, z) = vAllItems(ItemsChosen(i), 1) '**
added to list each combinations
z = z + 1 '**
added in the rage AC1:AL1
Next i
'and save it in the buffer
z = 1 '**
added
combins = combins + 1
Range("AI9").Value = Format(combins / Range("AI7").Value, "00.00%") '**
added
If Range("AN1").Value = Range("C1").Value Then '**
added ( adds to buffer only if the combination matches the criteria
[combinations, matches, frequency] )
BufferPtr = BufferPtr + 1
Buffer(BufferPtr) = Mid$(sValue, 3) & " " & Range("AN1").Value '**
added (& " " & Range("AN1").Value to find out the frequency)
End If
I am not sure if the approach/logic I followed is correct and need
feedback from VBA Experts like you. I have used a combination of VBA
and Excel formulas to fulfil my requirement. Please let me know if it
is correct and whether it can be changed to reduce more time and
improve on efficiency.
Maxi
Maxi wrote:
I am getting errors while submitting a reply and not sure if it went
through. Trying it again and it might appear twice.
I tried a lot but I am not getting a correct solution. I am still
trying to finish this. I would appreciate if you can help me with this.
For you it will be a cake walk.