Split numbers in 7 positions and make all possible sets adding 0 in it

[motilulla]

Split numbers in 7 positions and make all possible sets adding 0 in it</SPAN></SPAN>

Hello,</SPAN></SPAN>

I need to produce all possible set adding 0 in to given numbers (spiting in to 7 positions)</SPAN></SPAN>
Which finally sum should be = 7 for each of lines</SPAN></SPAN>

Here are the 16 lines from which I need to make all sets in the 7 positions adding the 0 it to it example sheet 1</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num	Total Num
4	7	6	5	5	4	4	4	3	3	3	3	2	2	2	2	2
5		1	1	2	1	2	3	1	2	3	4	1	2	3	4	5
6			1		1	1		1	1	1		1	1	1	1
7					1			1	1			1	1	1
8								1				1	1
9												1
Sheet1

Example if I use only 7 codes should create lines as shown in the example below</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	Total Num		n1	n2	n3	n4	n5	n6	n7	Sum
4	7		0	0	0	0	0	0	7	7
5			0	0	0	0	0	7	0	7
6			0	0	0	0	7	0	0	7
7			0	0	0	7	0	0	0	7
8			0	0	7	0	0	0	0	7
9			0	7	0	0	0	0	0	7
10			7	0	0	0	0	0	0	7
Sheet2

Example if I use only 6 & 1 codes should create lines as shown in the example below</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	Total Num		n1	n2	n3	n4	n5	n6	n7	Sum
4	6		0	0	0	0	0	6	1	7
5	1		0	0	0	0	0	1	6	7
6			0	0	0	0	6	0	1	7
7			0	0	0	0	6	1	0	7
8			0	0	0	0	1	0	6	7
9			0	0	0	0	1	6	0	7
10			0	0	0	6	0	0	1	7
11			0	0	0	6	0	1	0	7
12			0	0	0	6	1	0	0	7
13			0	0	0	1	0	0	6	7
14			0	0	0	1	0	6	0	7
15			0	0	0	1	6	0	0	7
16			0	0	6	0	0	0	1	7
17			0	0	6	0	0	1	0	7
18			0	0	6	0	1	0	0	7
19			0	0	6	1	0	0	0	7
20			0	0	1	0	0	0	6	7
21			0	0	1	0	0	6	0	7
22			0	0	1	0	6	0	0	7
23			0	0	1	6	0	0	0	7
24			0	6	0	0	0	0	1	7
25			0	6	0	0	0	1	0	7
26			0	6	0	0	1	0	0	7
27			0	6	0	1	0	0	0	7
28			0	6	1	0	0	0	0	7
29			0	1	0	0	0	0	6	7
30			0	1	0	0	0	6	0	7
31			0	1	0	0	6	0	0	7
32			0	1	0	6	0	0	0	7
33			0	1	6	0	0	0	0	7
34			6	0	0	0	0	0	1	7
35			6	0	0	0	0	1	0	7
36			6	0	0	0	1	0	0	7
37			6	0	0	1	0	0	0	7
38			6	0	1	0	0	0	0	7
39			6	1	0	0	0	0	0	7
40			1	0	0	0	0	0	6	7
41			1	0	0	0	0	6	0	7
42			1	0	0	0	6	0	0	7
43			1	0	0	6	0	0	0	7
44			1	0	6	0	0	0	0	7
45			1	6	0	0	0	0	0	7
Sheet3

And same way I need code could be generate for each of set is shown in the sheet1 through A:P as the used in the range A4:A9</SPAN></SPAN>

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Hello,</SPAN></SPAN>

Basically I need that the numbers are in the area A4:A9 out of these numbers make all possible permutations in the 7 positions which SUM is = 7 not necessary to fill the blanks with 0, may this helps </SPAN></SPAN>

Any advice please, thank you Regards, Moti</SPAN></SPAN>

 

[motilulla]

Hello,</SPAN></SPAN>

Mr.Excel search I find a code below under given link as per example it generate 2187 permutations with sum through 0 to 42 can it restricted so it can print out only rows just with sum = 7
https://www.mrexcel.com/forum/excel...-aca-caa-only-considered-one.html#post2349630

Please run the code with given example

Book1
	A	B	C	D	E	F	G	H	I	J
1	P	7
2	Comb	FALSE
3	Repet	TRUE
4
5	Set	0
6		1
7		6
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet8

Option Explicit
 
' PGC Set 2007
' Calculates and writes the Combinations / Permutations with/without repetition
' vElements - Array with the set elements (1 to n)
' p - number of elements in 1 combination/permutation
' bComb - True: Combinations, False: Permutations
' bRepet - True: with repetition, False: without repetition
' vResult - Array to hold 1 permutation/combination (1 to p)
' lRow - row number. the next combination/permutation is written in lRow+1
' vResultAll - Array to hold all the permutations/combinations (1 to Total, 1 to p)
' iElement - order of the element to process in case of combination
' iIndex - position of the next element in the combination/permutation
' Sub CombPerm() deals with the input / output
' Sub CombPermNP() generates the combinations / permutations
Sub CombPerm()
Dim rRng As Range, p As Integer
Dim vElements As Variant, vResult As Variant, vResultAll As Variant, lTotal As Long
Dim lRow As Long, bComb As Boolean, bRepet As Boolean
 
' Get the inputs and clear the result range (you may adjust for other locations)
Set rRng = Range("B5", Range("B5").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked
bComb = Range("B2")
bRepet = Range("B3")
 
Columns("D").Resize(, p + 1).Clear
 
' Error
If (Not bRepet) And (rRng.Count < p) Then
    MsgBox "With no repetition the number of elements of the set must be bigger or equal to p"
    Exit Sub
End If
 
' Set up the arrays for the set elements and the result
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
With Application.WorksheetFunction
    If bComb = True Then
            lTotal = .Combin(rRng.Count + IIf(bRepet, p - 1, 0), p)
    Else
        If bRepet = False Then lTotal = .Permut(rRng.Count, p) Else lTotal = rRng.Count ^ p
    End If
End With
ReDim vResult(1 To p)
ReDim vResultAll(1 To lTotal, 1 To p)
 
' Calculate the Combinations / Permutations
Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, 1, 1)
Range("D1").Resize(lTotal, p).Value = vResultAll  'you may adjust for other location
End Sub
 
Sub CombPermNP(ByVal vElements As Variant, ByVal p As Integer, ByVal bComb As Boolean, ByVal bRepet As Boolean, _
                             ByVal vResult As Variant, ByRef lRow As Long, ByRef vResultAll As Variant, ByVal iElement As Integer, ByVal iIndex As Integer)
Dim i As Integer, j As Integer, bSkip As Boolean
 
For i = IIf(bComb, iElement, 1) To UBound(vElements)
    bSkip = False
    ' in case of permutation without repetition makes sure the element is not yet used
    If (Not bComb) And Not bRepet Then
        For j = 1 To p
            If vElements(i) = vResult(j) And Not IsEmpty(vResult(j)) Then
                bSkip = True
                Exit For
            End If
        Next
    End If
 
    If Not bSkip Then
        vResult(iIndex) = vElements(i)
        If iIndex = p Then
            lRow = lRow + 1
            For j = 1 To p
                vResultAll(lRow, j) = vResult(j)
            Next j
        Else
            Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, i + IIf(bComb And bRepet, 0, 1), iIndex + 1)
        End If
    End If
Next i
End Sub

Please any help would be very much appreciated </SPAN></SPAN>


Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

</SPAN></SPAN>Hello, I did searched in goggle in the "Mr. Excel" there are many VBA combinations, permutations but I do not find any what exactly I am looking for, combinations, permutations with target sums</SPAN></SPAN>

Need help, thank you all</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

Bump, May be formula than can do the job will do.....

 

[motilulla]

Bump