I sort of understand, but to p45cal's comment ...
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
I sort of understand, but to p45cal's comment ...
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I was just scratching my head over the simplified Book2 example in post #35
Capture.JPG
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
Last edited by Paul_Hossler; 03-14-2021 at 09:59 AM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi p45cal,
Could you please have a look at the attached excel file as an exampleBook1.xlsx. I have left a note about the criteria.
Thanks
There are about 40 million 'acceptable' combinations.
Output is to the Immediate pane: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
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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)
Capture.JPG
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.comb.jpg
in this case k=6 and n is variable depends on the count of numbers in column "DATA".
Last edited by NIMANIMA50; 03-17-2021 at 07:06 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Try the attached.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.