VBA could build a combination with the set of 14 numbers

Kishan · Aug 24, 2017

Using Excel 2000

Hi,

I got table, which got 3 groups of 14 numbers & 14 sets each with 3 numbers
Building combinations in the set of 14 numbers it could make with min to max sum, which is in the range of 287 to 315 are listed in the cells U5:V33

3 groups - each group contain 14 numbers
Group-1 Find in the range D5:Q5
Group-2 Find in the range D6:Q6
Group-3 Find in the range D7:Q7

14 set - each set contain 3 numbers
Set-1 Find in the range D5:D7
Set-2 Find in the range E5:E7
Set-3 Find in the range F5:F7
Set-4 Find in the range G5:G7
Set-5 Find in the range H5:H7
Set-6 Find in the range I5:I7
Set-7 Find in the range J5:J7
Set-8 Find in the range K5:K7
Set-9 Find in the range L5:L7
Set-10 Find in the range M5:M7
Set-11 Find in the range N5:N7
Set-12 Find in the range O5:O7
Set-13 Find in the range P5:P7
Set-14 Find in the range Q5:Q7

Method of building combinations:
Pick numbers from any group but only 1 from the each set
For example...
If first chosen num is 1 (num-1 is from the 1st-set, so 2 & 3 can not be chosen as second num)
Second num must be chosen from the 2nd set if the second num is chosen 5 (num-5 is from the 2nd-set, so 4 & 6 can not be chosen as third num) and so on.... to build each combinations with set of 14 numbers.

My query is it possible to get VBA, which can generate combinations with desire SUM
For example...I want to get all combinations with SUM 293 that can be total 19.383
As per my layout if I set target sum in the cell C2 = 293 can I get all 19.383 combinations in the range D12:Q19394

I have generated manually 36 combinations just to show as the example data in the range D12:Q47

Book1
A B C D E F G H I J K L M N O P Q R S T U V
1 Target Sum
2 293
3 Min & Max Combinations Total
4 Set-1 Set-2 Set-3 Set-4 Set-5 Set-6 Set-7 Set-8 Set-9 Set-10 Set-11 Set-12 Set-13 Set-14 SUM Of Sum Combinations
5 Group-1 1 4 7 10 13 16 19 22 25 28 31 34 37 40 287 287 1
6 Group-2 2 5 8 11 14 17 20 23 26 29 32 35 38 41 288 14
7 Group-3 3 6 9 12 15 18 21 24 27 30 33 36 39 42 315 289 105
8 290 546
9 291 2.184
10 292 7.098
11 Combinations Set-1 Set-2 Set-3 Set-4 Set-5 Set-6 Set-7 Set-8 Set-9 Set-10 Set-11 Set-12 Set-13 Set-14 Sum 293 19.383
12 1 3 5 7 10 14 17 19 22 25 28 31 34 38 40 293 294 45.474
13 2 2 4 7 10 13 16 20 23 25 29 32 34 37 41 293 295 93.093
14 3 1 5 9 10 13 16 19 23 25 30 31 34 37 40 293 296 168.168
15 4 1 6 7 11 14 16 19 22 25 28 31 34 37 42 293 297 270.270
16 5 1 4 7 12 13 16 19 22 25 28 33 36 37 40 293 298 388.752
17 6 1 4 7 10 13 16 20 22 25 29 31 36 39 40 293 299 502.593
18 7 2 6 9 10 13 16 20 22 25 28 31 34 37 40 293 300 585.690
19 8 2 4 8 10 13 16 19 22 25 28 33 34 38 41 293 301 616.227
20 9 2 6 7 12 13 16 19 22 25 28 31 35 37 40 293 302 585.690
21 10 1 4 7 10 13 16 19 23 25 29 31 34 39 42 293 303 502.593
22 11 1 5 7 12 14 16 19 22 25 28 32 35 37 40 293 304 388.752
23 12 3 5 7 12 13 17 19 22 25 28 31 34 37 40 293 305 270.270
24 13 1 4 7 10 14 17 21 22 26 28 32 34 37 40 293 306 168.168
25 14 1 6 7 10 13 17 20 23 25 28 31 34 37 41 293 307 93.093
26 15 2 4 7 10 14 17 19 22 26 28 31 34 37 42 293 308 45.474
27 16 1 4 7 10 13 18 19 22 25 28 32 35 37 42 293 309 19.383
28 17 3 4 7 11 13 16 19 23 26 28 31 35 37 40 293 310 7.098
29 18 2 4 7 10 13 16 19 22 27 29 33 34 37 40 293 311 2.184
30 19 2 4 8 10 14 17 19 22 25 29 31 34 37 41 293 312 546
31 20 1 4 7 10 14 16 21 24 25 29 31 34 37 40 293 313 105
32 21 1 4 8 10 14 16 19 22 26 28 32 34 39 40 293 314 14
33 22 1 5 7 10 15 17 19 22 25 29 31 34 38 40 293 315 1
34 23 1 4 9 10 14 16 19 23 25 28 32 34 38 40 293
35 24 1 5 7 10 14 16 21 23 25 28 31 34 37 41 293
36 25 1 4 7 10 13 17 19 23 25 28 32 35 37 42 293
37 26 1 4 8 12 13 17 20 22 25 28 32 34 37 40 293
38 27 1 4 7 12 13 16 19 22 27 29 31 35 37 40 293
39 28 1 4 7 10 13 18 20 22 25 28 32 35 37 41 293
40 29 3 4 7 12 13 18 19 22 25 28 31 34 37 40 293
41 30 3 4 7 10 13 16 21 22 25 28 32 34 37 41 293
42 31 1 4 7 11 13 17 19 22 26 28 32 34 37 42 293
43 32 1 4 9 10 13 18 19 22 25 28 31 34 39 40 293
44 33 3 4 7 12 13 16 19 22 26 28 32 34 37 40 293
45 34 1 4 9 11 13 16 19 22 25 30 31 34 38 40 293
46 35 1 4 7 11 14 16 19 24 25 28 32 34 38 40 293
47 36 3 4 8 10 13 16 19 23 25 28 32 34 38 40 293
Sheet1

Thank you in advance

Kishan

Kishan · Aug 24, 2017

Hi, the information is shown in the column U:V, that is generated with the Function provided by Eric W, under this link
https://www.mrexcel.com/forum/excel...ations-could-made-each-sum-2.html#post4895441

Regards,
Kishan

Eric W · Aug 24, 2017

I still submit that there's no benefit to enumerating all the combinations, but if you really want the list, here's a macro. It requires your sheet set up as shown, C2 has the target sum, and the totals in U:V are required.

Code:

Public Sub ListCombs()
Dim ix(1 To 14) As Long, i As Long, target As Long, ulim As Long, ctr As Long, Results()

    target = Range("C2").Value
    ulim = WorksheetFunction.VLookup(target, Range("U5:V33"), 2)
    Range("C12:S" & 11 + ulim).ClearContents
    ReDim Results(1 To ulim, 1 To 17)
    ctr = 0
    
    For i = 1 To 14
        ix(i) = (i - 1) * 3 + 1
    Next i
    
MyLoop:
    If WorksheetFunction.Sum(ix) = target Then
        ctr = ctr + 1
        For i = 1 To 14
            Results(ctr, i + 1) = ix(i)
        Next i
        Results(ctr, 1) = ctr
        Results(ctr, 17) = target
        If ctr = ulim Then GoTo ImDone:
    End If
        
    For i = 1 To 14
        ix(i) = ix(i) + 1
        If ix(i) Mod 3 <> 1 Then Exit For
        ix(i) = ix(i) - 3
    Next i
    If i < 15 Then GoTo MyLoop:


ImDone:
    Range("C12:S" & 11 + ulim).Value = Results


End Sub

Kishan · Aug 24, 2017

Eric W said:
I still submit that there's no benefit to enumerating all the combinations, but if you really want the list, here's a macro. It requires your sheet set up as shown, C2 has the target sum, and the totals in U:V are required.

Wow Eric, speechless thank you so much for giving a great solution

it is spot on and fast too.

Please one more request as I am using very older version 2000 that has row limit 65536

As there are max 616.227 combinations with sum 301 to get listed all them, it need to add 9 more sheets or may be combinations divide in 65000
First set of 65000 start writing in columns AA12:AN64988, Second set of 65000 start writing in columns AP12:BC64988

I mean 14 columns then next + 1 & 14 and so on...

Is it possible Please?

Thank you

Good Luck

Regards,
Kishan

Eric W · Aug 25, 2017

Try:

Code:

Public Sub ListCombs()
Dim ix(1 To 14) As Long, i As Long, target As Long, ctr As Long, Results(1 To 65000, 1 To 17)
Dim PageNo As Long, c2 As Long


    Application.ScreenUpdating = False
    target = Range("C2").Value
    ctr = 0
    
    For i = 1 To 14
        ix(i) = (i - 1) * 3 + 1
    Next i
    
MyLoop:
    If WorksheetFunction.Sum(ix) = target Then
        ctr = ctr + 1
        c2 = ((ctr - 1) Mod 65000) + 1
        For i = 1 To 14
            Results(c2, i + 1) = ix(i)
        Next i
        Results(c2, 1) = ctr
        Results(c2, 17) = target
        If ctr Mod 65000 = 0 Then
            PageNo = PageNo + 1
            With Worksheets.Add
                .Range("A1:Q1").Value = Array("Counter", "Grp1", "Grp2", "Grp3", "Grp4", "Grp5", "Grp6", "Grp7", _
                                       "Grp8", "Grp9", "Grp10", "Grp11", "Grp12", "Grp13", "Grp14", "", "")
                .Cells(1, 17) = "Page " & PageNo
                .Range("A2:Q65001").Value = Results
            End With
            Erase Results
        End If
            
    End If
        
    For i = 1 To 14
        ix(i) = ix(i) + 1
        If ix(i) Mod 3 <> 1 Then Exit For
        ix(i) = ix(i) - 3
    Next i
    If i < 15 Then GoTo MyLoop:


    PageNo = PageNo + 1
    With Worksheets.Add
        .Range("A1:Q1").Value = Array("Counter", "Grp1", "Grp2", "Grp3", "Grp4", "Grp5", "Grp6", "Grp7", _
                                       "Grp8", "Grp9", "Grp10", "Grp11", "Grp12", "Grp13", "Grp14", "", "")
        .Cells(1, 17) = "Page " & PageNo
        .Range("A2:Q65001").Value = Results
    End With
    
    Application.ScreenUpdating = True


End Sub

Start it while your main page is visible, and put your target sum in C2.

Kishan · Aug 25, 2017

Eric W said:

Try:

Code:

Public Sub ListCombs()
Dim ix(1 To 14) As Long, i As Long, target As Long, ctr As Long, Results(1 To 65000, 1 To 17)
Dim PageNo As Long, c2 As Long


    Application.ScreenUpdating = False
    target = Range("C2").Value
    ctr = 0
    
    For i = 1 To 14
        ix(i) = (i - 1) * 3 + 1
    Next i
    
MyLoop:
    If WorksheetFunction.Sum(ix) = target Then
        ctr = ctr + 1
        c2 = ((ctr - 1) Mod 65000) + 1
        For i = 1 To 14
            Results(c2, i + 1) = ix(i)
        Next i
        Results(c2, 1) = ctr
        Results(c2, 17) = target
        If ctr Mod 65000 = 0 Then
            PageNo = PageNo + 1
            With Worksheets.Add
                .Range("A1:Q1").Value = Array("Counter", "Grp1", "Grp2", "Grp3", "Grp4", "Grp5", "Grp6", "Grp7", _
                                       "Grp8", "Grp9", "Grp10", "Grp11", "Grp12", "Grp13", "Grp14", "", "")
                .Cells(1, 17) = "Page " & PageNo
                .Range("A2:Q65001").Value = Results
            End With
            Erase Results
        End If
            
    End If
        
    For i = 1 To 14
        ix(i) = ix(i) + 1
        If ix(i) Mod 3 <> 1 Then Exit For
        ix(i) = ix(i) - 3
    Next i
    If i < 15 Then GoTo MyLoop:


    PageNo = PageNo + 1
    With Worksheets.Add
        .Range("A1:Q1").Value = Array("Counter", "Grp1", "Grp2", "Grp3", "Grp4", "Grp5", "Grp6", "Grp7", _
                                       "Grp8", "Grp9", "Grp10", "Grp11", "Grp12", "Grp13", "Grp14", "", "")
        .Cells(1, 17) = "Page " & PageNo
        .Range("A2:Q65001").Value = Results
    End With
    
    Application.ScreenUpdating = True


End Sub

Start it while your main page is visible, and put your target sum in C2.

Amazing! Eric W, You took a step ahead of my thought very well set-up, every page has header clearly informed by counter, group & including the page numbers.

code is a very speedy I tried with max sum 301 which makes 616.227 combinations code create 9 sheets each with 65000*9 =585.000 & the 10th one with the rest 31.227 It took just a seconds as quick as eye blink

I cannot thank you enough. It is a wow! Code

Good Luck

Have a great weekend

Kind Regards
Kishan

VBA could build a combination with the set of 14 numbers

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

VBA could build a combination with the set of 14 numbers

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock