Out of 14 numbers make all unique combinations in with only 6 numbers in the row

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,</SPAN></SPAN>

I got 14 numbers in cells A5:A18 these numbers are changeable every time but (but always will be the 14 numbers)</SPAN></SPAN>

I need VBA code, which can generate all possible unique combinations with 6 numbers in the columns D:I and sum of each row combination must be 6. Is it possible?</SPAN></SPAN>

Example data</SPAN></SPAN>


Book1
ABCDEFGHIJ
1
2
3
4Numbersn1n2n3n4n5n6SUM
500011046
600011406
710011226
810010416
910014016
1000014106
1140012216
1200004026
1320004206
1400002226
1520040206
1620040026
1710042006
1800020226
190022206
200111216
210110406
220110226
230114006
240112026
250112206
260104016
270104106
280102216
290140016
300140106
310120216
320122106
330402006
340400206
350202206
361110216
371112016
381112106
391104006
401100226
411102026
421102206
431140006
441120206
451040016
461040106
471002216
481020216
491022106
501400106
511202106
Sheet1


Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Sort by date Sort by votes
motilulla said:

This is just to clearly k = 6, I think this mean create permutations by 6 positions if I change k = 5, k = 4, k = 3 or k = 2 the code create permutations by the positions of k numbers. Why when I change K = 7, 8, 9 (I mean greeter than value 6) I get message "Run time error 7: out of memory"
Click to expand...

I was being a bit lazy in allocating memory. If k = 9 then I'm trying to allocate PERMUT(14,9) x 9 spaces, which clearly is way too much.

In fact, given you have only four numbers, with frequencies 6,4,2, and 1, we only need to allocate room for 48,342 results, which is the coefficient of x^9 in:

9!(1 + x) (1+ x + x^2/2!+x^3/3!) (1 + x + x^2/2! + x^3/3! + x^4/4!)(1 + x + x^2/2! + x^3/3! + x^4/4! + x^5/5! + x^6/6!)

We can generate all 48,342 using this revised code:

Code: 
Option Explicit
Dim k As Long, row As Long
Dim N As Variant, Permutations() As Variant
Sub Test()
    
    Dim Permutation() As Variant
    Dim rng As Range
    
    Set rng = Range("A5:B8")
    N = rng.Value
    k = 9
    row = 0
    ReDim Permutation(1 To k)
    ReDim Permutations(1 To GetCoefficients(rng, k)(k), 1 To k)
        
    Call GetPermutations(Permutation, 1)
    
    With Range("D5")
        .CurrentRegion.Offset(1).ClearContents
        With .Resize(row, k + 1)
            .Value = Permutations
           .Columns(k + 1).FormulaR1C1 = "=SUM(RC[-" & k & "]:RC[-1])"
            .Sort Key1:=.Columns(k + 1), Orientation:=xlTopToBottom, Order1:=xlAscending
        End With
    End With
        
End Sub
Sub GetPermutations(ByVal Permutation As Variant, col As Long)
    
    Dim i As Long, j As Long
    
    For i = 1 To UBound(N)
        If N(i, 2) > 0 Then
            Permutation(col) = N(i, 1)
            If col < k Then
                N(i, 2) = N(i, 2) - 1
                Call GetPermutations(Permutation, col + 1)
                N(i, 2) = N(i, 2) + 1
            Else
                row = row + 1
                For j = 1 To k
                    Permutations(row, j) = Permutation(j)
                Next j
            End If
        End If
    Next i
    
End Sub
Function GetCoefficients(rng As Range, N As Long) As Double()

    Dim i As Long, j As Long, k As Long, counter As Long
    Dim temp1() As Double, temp2() As Double, Result() As Double
    ReDim temp1(1 To rng.Rows.Count, 0 To Application.Sum(rng.Columns(2)))
    ReDim Result(0 To Application.Sum(rng.Columns(2)))
    
    For i = 1 To UBound(temp1)
        temp1(i, 0) = 1
        temp1(i, 1) = 1
        For j = 2 To rng(i, 2).Value
            temp1(i, j) = temp1(i, j - 1) / j
        Next j
    Next i
    
    For j = 0 To rng(1, 2).Value
        Result(j) = temp1(1, j) * Application.Fact(N)
    Next j

    counter = rng(1, 2).Value
    For i = 2 To UBound(temp1)
        ReDim temp2(0 To Application.Sum(rng.Columns(2)))
        For j = 0 To counter
            For k = 0 To rng(i, 2).Value
                temp2(j + k) = temp2(j + k) + Result(j) * temp1(i, k)
            Next k
        Next j
        Result = temp2
        counter = counter + rng(i, 2).Value
    Next i
    
    GetCoefficients = Result

End Function
 
Last edited:
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
.. where this is the layout:


Book1
ABCDEFGHIJKLM
4NumberFreqn1n2n3n4n5n6n7n8n9SUM
5060000001113
6140000010113
7230000011013
8410000011103
90000100113
100000101013
110000101103
12etc ..
Sheet1
 
Last edited:
Upvote 0
Should have said:

In fact, given you have only four numbers, with frequencies 6,4,3, and 1, we only need to allocate room for 48,342 results, which is the coefficient of x^9 in:

9!(1 + x) (1+ x + x^2/2!+x^3/3!) (1 + x + x^2/2! + x^3/3! + x^4/4!)(1 + x + x^2/2! + x^3/3! + x^4/4! + x^5/5! + x^6/6!)
 
Upvote 0
StephenCrump, thank you for sending me the revised code. As I am out for vacation till the 18th of January, this is why I don't have access to my desktop computer, thus i will reply as soon as i get back home.

Have a great weekend, Good Luck.

Kind Regards,
Moti
 
Upvote 0
StephenCrump said:
.. where this is the layout:

ABCDEFGHIJKLM

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5 "><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]Freq[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]n9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]SUM[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]etc ..[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>
Click to expand...
StephenCrump, yes I like this getting layout as you shown and sum are sorted in ascending order that is very perfect! :)
 
Upvote 0
StephenCrump said:
Should have said:

In fact, given you have only four numbers, with frequencies 6,4,3, and 1, we only need to allocate room for 48,342 results, which is the coefficient of x^9 in:

9!(1 + x) (1+ x + x^2/2!+x^3/3!) (1 + x + x^2/2! + x^3/3! + x^4/4!)(1 + x + x^2/2! + x^3/3! + x^4/4! + x^5/5! + x^6/6!)
Click to expand...
StephenCrump, I am too weak in maths formula and to solve such a complicate equations uff very difficult
 
Upvote 0
StephenCrump said:
I was being a bit lazy in allocating memory. If k = 9 then I'm trying to allocate PERMUT(14,9) x 9 spaces, which clearly is way too much.

In fact, given you have only four numbers, with frequencies 6,4,2, and 1, we only need to allocate room for 48,342 results, which is the coefficient of x^9 in:

9!(1 + x) (1+ x + x^2/2!+x^3/3!) (1 + x + x^2/2! + x^3/3! + x^4/4!)(1 + x + x^2/2! + x^3/3! + x^4/4! + x^5/5! + x^6/6!)

We can generate all 48,342 using this revised code:

Code: 
Option Explicit
Dim k As Long, row As Long
Dim N As Variant, Permutations() As Variant
Sub Test()
    
    Dim Permutation() As Variant
    Dim rng As Range
    
    Set rng = Range("A5:B8")
    N = rng.Value
    k = 9
    row = 0
    ReDim Permutation(1 To k)
    ReDim Permutations(1 To GetCoefficients(rng, k)(k), 1 To k)
        
    Call GetPermutations(Permutation, 1)
    
    With Range("D5")
        .CurrentRegion.Offset(1).ClearContents
        With .Resize(row, k + 1)
            .Value = Permutations
           .Columns(k + 1).FormulaR1C1 = "=SUM(RC[-" & k & "]:RC[-1])"
            .Sort Key1:=.Columns(k + 1), Orientation:=xlTopToBottom, Order1:=xlAscending
        End With
    End With
        
End Sub
Sub GetPermutations(ByVal Permutation As Variant, col As Long)
    
    Dim i As Long, j As Long
    
    For i = 1 To UBound(N)
        If N(i, 2) > 0 Then
            Permutation(col) = N(i, 1)
            If col < k Then
                N(i, 2) = N(i, 2) - 1
                Call GetPermutations(Permutation, col + 1)
                N(i, 2) = N(i, 2) + 1
            Else
                row = row + 1
                For j = 1 To k
                    Permutations(row, j) = Permutation(j)
                Next j
            End If
        End If
    Next i
    
End Sub
Function GetCoefficients(rng As Range, N As Long) As Double()

    Dim i As Long, j As Long, k As Long, counter As Long
    Dim temp1() As Double, temp2() As Double, Result() As Double
    ReDim temp1(1 To rng.Rows.Count, 0 To Application.Sum(rng.Columns(2)))
    ReDim Result(0 To Application.Sum(rng.Columns(2)))
    
    For i = 1 To UBound(temp1)
        temp1(i, 0) = 1
        temp1(i, 1) = 1
        For j = 2 To rng(i, 2).Value
            temp1(i, j) = temp1(i, j - 1) / j
        Next j
    Next i
    
    For j = 0 To rng(1, 2).Value
        Result(j) = temp1(1, j) * Application.Fact(N)
    Next j

    counter = rng(1, 2).Value
    For i = 2 To UBound(temp1)
        ReDim temp2(0 To Application.Sum(rng.Columns(2)))
        For j = 0 To counter
            For k = 0 To rng(i, 2).Value
                temp2(j + k) = temp2(j + k) + Result(j) * temp1(i, k)
            Next k
        Next j
        Result = temp2
        counter = counter + rng(i, 2).Value
    Next i
    
    GetCoefficients = Result

End Function
Click to expand...
StephenCrump, I am very happy it is working all right with any positions whatever I do select within the k.</SPAN></SPAN>

Thank you so much for you're helping to me and solving my request as I required.</SPAN></SPAN>

Have a nice weekend. Good Luck</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti :-D</SPAN></SPAN>
 
Upvote 0
motilulla said:
I am very happy it is working all right with any positions whatever I do select within the k.
Click to expand...

Great. I am glad it's working for you.

StephenCrump said:
.. the coefficient of x^9 in:

9!(1 + x) (1+ x + x^2/2!+x^3/3!) (1 + x + x^2/2! + x^3/3! + x^4/4!)(1 + x + x^2/2! + x^3/3! + x^4/4! + x^5/5! + x^6/6!)
Click to expand...

motilulla said:
I am too weak in maths formula and to solve such a complicate equations uff very difficult
Click to expand...

And I am too lazy :). You've probably worked out by now - that's why I wrote the GetCoefficients function.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
628
libelouli
L
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
209
MARZIOTULLIO
M
E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
483
Micron
M
M
  • Solved
Need help modify to pick any first number of my choice
Replies
6
Views
600
motilulla
M
B
  • Question Question
Count number of rows where a dynamically growing set of columns has the specified value
Replies
0
Views
446
Bries
B

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top