I sort of understand, but to p45cal's comment ...
Quote:
What on earth are you going to do with 6.25 million rows of data?!
it would seem to me that just listing the acceptable combinations would be better
Printable View
I sort of understand, but to p45cal's comment ...
Quote:
What on earth are you going to do with 6.25 million rows of data?!
it would seem to me that just listing the acceptable combinations would be better
If I've understood what Paul means by acceptable combinations, my 6.25 million rows are the acceptable ones, the macro has excluded some 13 million already!
What on earth can a human do with more than 6 million rows of data - I'd have difficulty handling 10 rows!
I was just scratching my head over the simplified Book2 example in post #35
Attachment 28105
There were 12 'results', but only one was 'Acceptable' (approx 8%). It appeared to be more a traverse problem to find an acceptable path than a combinations problem. Guess I was wrong
In the full example and using p45cal's macro the 6 columns of numbers look like there would be 14 x 19 x 19 x19 x20 x 11 = 21,125,720 to be checked, with 6,247,034 Acceptable (approx 30%)
Edit:
I'm still wondering what 6M+ results could be used for
Hi p45cal,
Could you please have a look at the attached excel file as an exampleAttachment 28117. I have left a note about the criteria.
Thanks
There are about 40 million 'acceptable' combinations.
Output is to the Immediate pane:Code:Sub blah()
For a = 1 To 53
For b = 2 To 54
If b > a Then
For c = 3 To 55
If c > b Then
For d = 4 To 56
If d > c Then
For e = 5 To 57
If e > d Then
For f = 6 To 58
If f > e Then
Debug.Print a, b, c, d, e, f
End If
Next f
End If
Next e
End If
Next d
End If
Next c
End If
Next b
Next a
End Sub
P45cal,
Thank you for your response. However there are some issues with your code.
1. it considers the numbers in DATA columns as fixed (1-58) but this columns has variable and mixed numbers (My fault I did not clearly explained at first).
2. I tried to run the code but Excel got frozen so I have to close it, but by looking at the code, it tries to list all combination in one sheet and separated by ",". I need the combinations to be placed in different cells (for instant A-F) and if can not fit in one sheet it will be split in different sheets (workbooks preferably) , like your previous code.
I have attached another file , I hope this file will be clear.
Thanks again
I think it's easier to generate combinations down to row 1M and then move over 7 columns for the next 1M instead of messing with adding worksheets / workbooks (just my 2 cents)
Attachment 28123
This just does data in the same order as it's listed in Col A and will get messed up if a number is repeated in Data
Code:Option Explicit
Sub Generate()
Dim r As Range
Dim v As Variant
Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long, n5 As Long, n6 As Long
Dim rowCount As Long, colCount As Long
Dim N As Long
With Worksheets("Sheet2")
Set r = .Range("A1")
Set r = Range(r, r.End(xlDown))
v = Application.WorksheetFunction.Transpose(r)
N = UBound(v)
rowCount = 1
colCount = 3
For n1 = 1 To N - 5
For n2 = n1 + 1 To N - 4
For n3 = n2 + 1 To N - 3
For n4 = n3 + 1 To N - 2
For n5 = n4 + 1 To N - 1
For n6 = n5 + 1 To N
.Cells(rowCount, colCount).Value = v(n1)
.Cells(rowCount, colCount + 1).Value = v(n2)
.Cells(rowCount, colCount + 2).Value = v(n3)
.Cells(rowCount, colCount + 3).Value = v(n4)
.Cells(rowCount, colCount + 4).Value = v(n5)
.Cells(rowCount, colCount + 5).Value = v(n6)
rowCount = rowCount + 1
If rowCount Mod 1000 = 0 Then
Application.StatusBar = v(n1) & "," & v(n2) & "," & v(n3) & "," & v(n4) & "," & v(n5) & "," & v(n6)
DoEvents
End If
If rowCount = 1000001 Then
rowCount = 1
colCount = colCount + 7
End If
Next n6
Next n5
Next n4
Next n3
Next n2
Next n1
End With
Application.StatusBar = False
MsgBox "Done"
End Sub
Write a second macro to take each million row chunk and put into a separate workbook, although I still fail to see how 40 x 10^6 six-tuples would ever be easy to work with.
Did you ever say just what the ultimate purpose was, or did I miss it?
This is ludicrous.
1. Since the values don't have to increase any more from left to right, are (for example) both
1,2,3,4,5,6
and
1,2,3,4,6,5
required in the list?
(just the last 2 numbers are switched)
2. Will the starting column of numbers contain duplicates?
p45cal,
The order of combination is not required. as you said, 1,2,3,4,5,6 and 1,2,3,4,6,5 are the same and only one needs to be listed.
this is just combination without repetition that we use this formula to calculate the total number of combination.Attachment 28125
in this case k=6 and n is variable depends on the count of numbers in column "DATA".
I'll take that as a yes. It does matter because it needs to be catered for in the code you're asking for; it just means that the process of finding the combinations could be a lot longer, or that we have to do some deduplicating before searching for combinations.
Try the attached.
Your last attachment works fine here. You'll have to wait a bit.
If you want to reassure yourself that it's working, change the line:
ResultBlockSize = 1000000
to say:
ResultBlockSize = 100000
which should produce some 24 new workbooks with your data.
I'm presuming the file I attached to msg#58 worked OK.