Need only resume how much combinations can be made with each sum

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
Office Version
  1. 2010
Hello,

In the example below columns C:I in the cells C4:I12 got 9 numbers in each columns the all combination will reach 9^7 = 4,782,969 which minimum sum is 0 and max sum 162 I require a code which can only give me how much combinations could be made with each sum through 0 To 162 and the all sum total will be reach 4,782,696
Example….

*ABCDEFGHIJKLM
1 Max Each Col2712942231633162
2 Total Combi4.782.696
3 n1n2n3n4n5n6n7 SumCombinations
4 1211143 0?
5 0620812 1?
6 2124618108 2?
7 275742360 3?
8 1248223146 4?
9 630269717 5?
10 31312601 6?
11 1199112933 7?
12 9054001619 8?
13 9?
14 10?
15 11?
16 12?
17 13?
18 14?
19 15?
20 16?
21 17?
22 18?
23 19?
159 155?
160 156?
161 157?
162 158?
163 159?
164 160?
165 161?
166 162?
167
168
169

<colgroup><col><col><col><col span="7"><col><col><col><col></colgroup><tbody>
</tbody>

Thank you all

Excel 2000
Regards,
Moti
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
motilulla said:
Hello again, i got the quote from Akuini which is in the link shown below. It generates all 4,782,696 combinations with each sum. But now i need to know how many combinations are with each sum in total well summarised.

https://www.mrexcel.com/forum/excel...-each-column-row-post5193625.html#post5193625
May this help to resolve my query.

Regards,Moti
Click to expand...
Hello, I want to say that the "Akuini code" generate all the 9^7 combinations but it is difficult to count by there sums

So far now I am looking any one can help to give a code which can only give the summary of the "sums only"

I am not sure weather is it possible to do without generating all the combinations ?

Regards,
Moti
 
Upvote 0
motilulla said:
Hello again, i got the quote from Akuini which is in the link shown below. It generates all 4,782,696 combinations with each sum. But now i need to know how many combinations are with each sum in total well summarised.

https://www.mrexcel.com/forum/excel...-each-column-row-post5193625.html#post5193625
May this help to resolve my query.

Regards,
Moti
Click to expand...

Try this:
Code: 
Sub a1081305a()
'https://www.mrexcel.com/forum/excel-questions/1081305-need-only-resume-how-much-combinations-can-made-each-sum.html
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, n As Long
Dim va, vb, vc, vd, ve, vf, vx
Dim dic As Object

Application.ScreenUpdating = False
vz = Range("C4", Cells(Rows.count, "C").End(xlUp))
va = Range("D4", Cells(Rows.count, "D").End(xlUp))
vb = Range("E4", Cells(Rows.count, "E").End(xlUp))
vc = Range("F4", Cells(Rows.count, "F").End(xlUp))
vd = Range("G4", Cells(Rows.count, "G").End(xlUp))
ve = Range("H4", Cells(Rows.count, "H").End(xlUp))
vf = Range("I4", Cells(Rows.count, "I").End(xlUp))


ReDim vx(1 To 4782969, 1 To 1)

For z = 1 To UBound(vz)
    For a = 1 To UBound(va)
        For b = 1 To UBound(vb)
            For c = 1 To UBound(vc)
                For d = 1 To UBound(vd)
                    For e = 1 To UBound(ve)
                        For f = 1 To UBound(vf)
                        n = n + 1
    vx(n, 1) = vz(z, 1) + va(a, 1) + vb(b, 1) + vc(c, 1) + vd(d, 1) + ve(e, 1) + vf(f, 1)
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Set dic = CreateObject("scripting.dictionary")
    For i = 0 To 162
       dic(i) = 0
    Next

For i = 1 To UBound(vx, 1)
dic(vx(i, 1)) = dic(vx(i, 1)) + 1
Next

Range("L4").Resize(dic.count, 2) = Application.Transpose(Array(dic.Keys, dic.Items))

Application.ScreenUpdating = True


End Sub
 
Upvote 0
Akuini said:
Try this:
Code: 
Sub a1081305a()
'https://www.mrexcel.com/forum/excel-questions/1081305-need-only-resume-how-much-combinations-can-made-each-sum.html
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, n As Long
Dim va, vb, vc, vd, ve, vf, vx
Dim dic As Object

Application.ScreenUpdating = False
vz = Range("C4", Cells(Rows.count, "C").End(xlUp))
va = Range("D4", Cells(Rows.count, "D").End(xlUp))
vb = Range("E4", Cells(Rows.count, "E").End(xlUp))
vc = Range("F4", Cells(Rows.count, "F").End(xlUp))
vd = Range("G4", Cells(Rows.count, "G").End(xlUp))
ve = Range("H4", Cells(Rows.count, "H").End(xlUp))
vf = Range("I4", Cells(Rows.count, "I").End(xlUp))


ReDim vx(1 To 4782969, 1 To 1)

For z = 1 To UBound(vz)
    For a = 1 To UBound(va)
        For b = 1 To UBound(vb)
            For c = 1 To UBound(vc)
                For d = 1 To UBound(vd)
                    For e = 1 To UBound(ve)
                        For f = 1 To UBound(vf)
                        n = n + 1
    vx(n, 1) = vz(z, 1) + va(a, 1) + vb(b, 1) + vc(c, 1) + vd(d, 1) + ve(e, 1) + vf(f, 1)
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Set dic = CreateObject("scripting.dictionary")
    For i = 0 To 162
       dic(i) = 0
    Next

For i = 1 To UBound(vx, 1)
dic(vx(i, 1)) = dic(vx(i, 1)) + 1
Next

Range("L4").Resize(dic.count, 2) = Application.Transpose(Array(dic.Keys, dic.Items))

Application.ScreenUpdating = True


End Sub
Click to expand...
Akuini, sorry for being so late with my response; I was out of town. </SPAN></SPAN>This is it what I wanted resume only of each sum, the code is magical I am surprised how did you made without generating none combinations Wow! Akuini </SPAN></SPAN>

Thank you so much for your kind help. </SPAN></SPAN>

Have a nice weekend </SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti :biggrin:</SPAN></SPAN>
 
Upvote 0
Hello Akuini, code worked very well with post#1, max data can be 9 in the each column and 7 in the row 9^7 but every time I do not get the same data, may be some time I have only one number in the few columns as this change the max sum so I replaced the code line below</SPAN></SPAN>
Code: 
</SPAN></SPAN>
This</SPAN></SPAN>
'For i = 0 To 162</SPAN></SPAN>
 
To this</SPAN></SPAN>
For i = 0 To Range("J1").Value</SPAN></SPAN>
</SPAN></SPAN>


Book1
ABCDEFGHIJKLM
1Max Each Col131854220219128
2
3n1n2n3n4n5n6n7SumCombinations
42354619
501202105
6118893
7132422021
8
9
10
11
12
13
14
15
16
Sheet1


And when I run the with above scenario code got stuck with following line</SPAN></SPAN>

Code: 
</SPAN></SPAN>
For b = 1 To UBound(vb)</SPAN></SPAN>
</SPAN></SPAN>
Pleases could you check </SPAN></SPAN>

Thank you </SPAN></SPAN>

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

By the way also having the problem with the code is attached in the following link do not generate combinations it stuck with the above scenarios</SPAN></SPAN>
https://www.mrexcel.com/forum/excel...y-1-number-each-column-row-2.html#post5193625</SPAN></SPAN>
 
Last edited:
Upvote 0
motilulla said:
Hello Akuini, code worked very well with post#1, max data can be 9 in the each column and 7 in the row 9^7 but every time I do not get the same data, may be some time I have only one number in the few columns as this change the max sum so I replaced the code line below
Click to expand...

Try this:
Code: 
Sub a1081305b()
'https://www.mrexcel.com/forum/excel-questions/1081305-need-only-resume-how-much-combinations-can-made-each-sum.html
Dim a As Long, b As Long, c As Long, d As Long, e As Long
Dim f As Long, g As Long, n As Long
Dim va, vx
Dim dic As Object

Application.ScreenUpdating = False

rr = Range("C:I").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
va = Range(Cells(4, "C"), Cells(rr, "I"))
y = UBound(va, 1)

ReDim vx(y ^ 7, 1 To 1)

For a = 1 To y
If va(a, 1) = "" Then Exit For
    For b = 1 To y
    If va(b, 2) = "" Then Exit For
        For c = 1 To y
        If va(c, 3) = "" Then Exit For
            For d = 1 To y
            If va(d, 4) = "" Then Exit For
                For e = 1 To y
                If va(e, 5) = "" Then Exit For
                    For f = 1 To y
                    If va(f, 6) = "" Then Exit For
                        For g = 1 To y
                        If va(g, 7) = "" Then Exit For
                            n = n + 1
                            vx(n, 1) = va(a, 1) + va(b, 2) + va(c, 3) + va(d, 4) _
                                + va(e, 5) + va(f, 6) + va(g, 7)
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Set dic = CreateObject("scripting.dictionary")
    For i = 0 To Range("J1")
       dic(i) = 0
    Next

For i = 1 To n
dic(vx(i, 1)) = dic(vx(i, 1)) + 1
Next

Range("L4").Resize(dic.count, 2) = Application.Transpose(Array(dic.Keys, dic.Items))

Application.ScreenUpdating = True


End Sub
 
Upvote 0
motilulla said:
Hello Akuini, code worked very well with post#1, max data can be 9 in the each column and 7 in the row 9^7 but every time I do not get the same data, may be some time I have only one number in the few columns as this change the max sum so I replaced the code line below</SPAN></SPAN>
Click to expand...

Akuini said:
Try this:
Code: 
Sub a1081305b()
'https://www.mrexcel.com/forum/excel-questions/1081305-need-only-resume-how-much-combinations-can-made-each-sum.html
Dim a As Long, b As Long, c As Long, d As Long, e As Long
Dim f As Long, g As Long, n As Long
Dim va, vx
Dim dic As Object

Application.ScreenUpdating = False

rr = Range("C:I").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
va = Range(Cells(4, "C"), Cells(rr, "I"))
y = UBound(va, 1)

ReDim vx(y ^ 7, 1 To 1)

For a = 1 To y
If va(a, 1) = "" Then Exit For
    For b = 1 To y
    If va(b, 2) = "" Then Exit For
        For c = 1 To y
        If va(c, 3) = "" Then Exit For
            For d = 1 To y
            If va(d, 4) = "" Then Exit For
                For e = 1 To y
                If va(e, 5) = "" Then Exit For
                    For f = 1 To y
                    If va(f, 6) = "" Then Exit For
                        For g = 1 To y
                        If va(g, 7) = "" Then Exit For
                            n = n + 1
                            vx(n, 1) = va(a, 1) + va(b, 2) + va(c, 3) + va(d, 4) _
                                + va(e, 5) + va(f, 6) + va(g, 7)
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Set dic = CreateObject("scripting.dictionary")
    For i = 0 To Range("J1")
       dic(i) = 0
    Next

For i = 1 To n
dic(vx(i, 1)) = dic(vx(i, 1)) + 1
Next

Range("L4").Resize(dic.count, 2) = Application.Transpose(Array(dic.Keys, dic.Items))

Application.ScreenUpdating = True


End Sub
Click to expand...
Akuini, no words to describe tested using full esquire 9*7 = 63 numbers in the range C4:I12 it worked like magic, even it works with single line here are the results below Amazing coding!</SPAN></SPAN>


Book1
ABCDEFGHIJKLM
1Max Each Col235461930
2
3n1n2n3n4n5n6n7SumCombinations
4235461900
510
620
730
840
950
1060
1170
1280
1390
14100
15110
16120
17130
18140
19150
20160
21170
22180
23190
24200
25210
26220
27230
28240
29250
30260
31270
32280
33290
34301
Sheet2


I am sorry for the giving you a trouble. Thank you very much for rebuilding fully code newly again. I appreciate for your time and help</SPAN></SPAN>

Good Luck</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti :biggrin:</SPAN></SPAN>
 
Last edited:
Upvote 0
Your welcome & thanks for the reply
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
help with combinations
Replies
3
Views
224
skillilea
S
T
  • Question
Combining long formulas in Excel
2
Replies
18
Views
429
tzav
T
P
  • Question
vba at each change
2
Replies
15
Views
684
kevin9999
K
J
  • Solved
Fill numbers based on Sum
Replies
7
Views
358
kevin9999
K
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A

Forum statistics

Threads
1,215,025
Messages
6,122,731
Members
449,093
Latest member
Mnur

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