Open office calc combinations
dfdfdfdf
-
yg_be Posted messages 23437 Registration date Status Contributeur Last intervention -
yg_be Posted messages 23437 Registration date Status Contributeur Last intervention -
Hello,
I would like to generate all the combinations of 5 digits out of 50 in Excel. Could you help me?
I don't know anything about it, and I don't know how to create or run a macro.
Thank you.
I would like to generate all the combinations of 5 digits out of 50 in Excel. Could you help me?
I don't know anything about it, and I don't know how to create or run a macro.
Thank you.
6 réponses
It's more complicated
I'll try to take a look at this in my spare time
--
"Imagination is more important than knowledge." A. Einstein
I'll try to take a look at this in my spare time
--
"Imagination is more important than knowledge." A. Einstein
yg_be
Posted messages
23437
Registration date
Status
Contributeur
Last intervention
Ambassadeur
1 588
Hello, what kind of result are you expecting?
Private Sub kombi() Dim f As Worksheet, nlig As Long, i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, i5 As Integer Set f = ThisWorkbook.Sheets("combi") nlig = 1 For i1 = 1 To 50 For i2 = 50 To i1 + 1 Step -1 For i3 = i2 + 1 To 50 For i4 = 50 To i3 + 1 Step -1 For i5 = i4 + 1 To 50 f.Cells(nlig, 1) = i1 f.Cells(nlig, 2) = i2 f.Cells(nlig, 3) = i3 f.Cells(nlig, 4) = i4 f.Cells(nlig, 5) = i5 nlig = nlig + 1 Next i5 Next i4 Next i3 Next i2 Next i1 End Sub
Hello
Here it is:
https://mon-partage.fr/f/3RHZ5m9V/
The results are displayed in 3 columns due to the number exceeding the number of cells in height in Excel
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
Here it is:
https://mon-partage.fr/f/3RHZ5m9V/
The results are displayed in 3 columns due to the number exceeding the number of cells in height in Excel
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
Re
Replace the macro with this one
From another sheet, run the macro from the Developer ribbon - Macros - combi - Run
and be patient: the macro takes several minutes to execute given the number of writes to be done.
Replace the macro with this one
Sub combi() y = 1 For a = 1 To 46 For b = a + 1 To 47 For c = b + 1 To 48 For d = c + 1 To 49 For e = d + 1 To 50 x = x + 1 If x > 1000000 Then x = 1: y = y + 7 Cells(x, y) = a Cells(x, y + 1) = b Cells(x, y + 2) = c Cells(x, y + 3) = d Cells(x, y + 4) = e Next Next Next Next Next End Sub
From another sheet, run the macro from the Developer ribbon - Macros - combi - Run
and be patient: the macro takes several minutes to execute given the number of writes to be done.
Hello, could you help me now to create a program that is practically similar but with constraints:
1) I want to provide a list of numbers
2) to make a combination of 5 digits from those I would have listed
-No repetition of the same digit in a combination
generating only combinations where the sum of the 5 digits is between 130 and 160
And that these combinations have a gap between each digit that I would determine, for example, we have a combination a b c d e I want that a+b+c+d+e = [130;160] and that b-a =[5;10], c-a=[12-14], d-a.., e-a.., etc
Thank you in advance for the time you will be able to allocate, and I imagine this will be quite lengthy
Best regards
1) I want to provide a list of numbers
2) to make a combination of 5 digits from those I would have listed
-No repetition of the same digit in a combination
generating only combinations where the sum of the 5 digits is between 130 and 160
And that these combinations have a gap between each digit that I would determine, for example, we have a combination a b c d e I want that a+b+c+d+e = [130;160] and that b-a =[5;10], c-a=[12-14], d-a.., e-a.., etc
Thank you in advance for the time you will be able to allocate, and I imagine this will be quite lengthy
Best regards
Hello
Yes, the macro works but depending on the numbers and the entered min and max conditions, there may be no solution at all
So what do you mean by I can't execute the macro, : can't you run it or does nothing come out?
It is normal for the solutions to be ordered according to the numbers in the list since the macro reads them in order so that none is forgotten, that the same number is not taken into account twice in the same solution, and that all combinations are considered, so I don't see why only part of the solutions would be returned
--
"Imagination is more important than knowledge." A. Einstein
Yes, the macro works but depending on the numbers and the entered min and max conditions, there may be no solution at all
So what do you mean by I can't execute the macro, : can't you run it or does nothing come out?
It is normal for the solutions to be ordered according to the numbers in the list since the macro reads them in order so that none is forgotten, that the same number is not taken into account twice in the same solution, and that all combinations are considered, so I don't see why only part of the solutions would be returned
--
"Imagination is more important than knowledge." A. Einstein
and wow, beautiful program