Conditional combination's pick only "1" number from each column in the row

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,360
Office Version
  1. 2010
Conditional combination's pick only "1" number from each column in the row <o:p></o:p>
<o:p></o:p>
Hello,<o:p></o:p>
<o:p></o:p>
I have some numbers in column D:J (each column has variable quantity of numbers in it) <o:p></o:p>
In the column D only 1 number which is 2 (this is like a joker will be used with all the columns numbers to create combinations) <o:p></o:p>
In the column E, G, H, I, J there are 2 numbers and in column F there are 4 numbers<o:p></o:p>
<o:p></o:p>
Require VBA which could make all Conditional combination's picking only "1" number from each column in the row (Conditional combination's) I mean for example in the row can not be used 2 number from any columns, 2 is a common number which will be combined with all column number to create all combinations<o:p></o:p>
<o:p></o:p>
Some example rows are Conditional combination's listed below in the column N:U <o:p></o:p>


Book1
ABCDEFGHIJKLMNOPQRSTU
1
2
3n1n2n3n4n5n6n7R1R2R3R4R5R6R7Sum
42210842221084219
56262318261084223
63221684225
792210234234
8221081216
9221084825
10262084224
11261684229
122610234238
13261084829
14222684226
152220234235
16222081217
17222084826
Sheet10
 <o:p></o:p>
<o:p></o:p>
Thank you all<o:p></o:p>
<o:p></o:p>
Excel 2000<o:p></o:p>
Regards,
Moti
 
Sort by date Sort by votes
Akuini said:
Ok, try this:
- it generates all the combinantion in sheet2
- the result does not include the sum of each combination, but if you need it we can modify the code.

Code: 
Sub a1080399d()
'https://www.mrexcel.com/forum/excel-questions/1080399-conditional-combinations-pick-only-1-number-each-column-row.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


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


'mn = Range("B1")
'mx = Range("B2")

ax = 65000
ReDim vx(1 To ax, 1 To 250)
k = 1
For z = 1 To UBound(vz)
If vz(z, 1) = "" Then Exit For
    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)
                        'dSum = vz(z, 1) + va(a, 1) + vb(b, 1) + vc(c, 1) + vd(d, 1) + ve(e, 1) + vf(f, 1)
                        'If dSum >= mn And dSum <= mx Then
                        'If dSum = mn Then
                            n = n + 1
                            vx(n, k) = vz(z, 1) & "|" & va(a, 1) & "|" & vb(b, 1) & "|" & vc(c, 1) & "|" _
                            & vd(d, 1) & "|" & ve(e, 1) & "|" & vf(f, 1)
                       
                        If n Mod 65000 = 0 Then n = 0: k = k + 2
                        
                        'End If
                        Next
                    Next
                Next
            Next
        Next
    Next
Next


Sheets("Sheet2").Range("A2").Resize(ax, k) = vx
Application.ScreenUpdating = True


End Sub
Click to expand...
Akuini, I am very happy with your code which is working perfectly! It has worked as per request.

This code starts listing from combinations A2. Please, could it be done so that the code can generate the combinations between desired minimum and maximum sums. Apart from that, can i have the sum of each combinations into the column next to it. I want that the combinations can start from column M2 so i can use columns A to K to list my data as shown in the example below:

*ABCDEFGHIJKLMN
1Min34Max Each Col2712942231633162 Sum
2Max34 1|2|1|1|1|4|313
3 n1n2n3n4n5n6n7 1|2|1|1|1|4|212
4 1211143 1|2|1|1|1|4|818
5 0620812 1|2|1|1|1|4|010
6 2124618108 1|2|1|1|1|4|616
7 275742360 1|2|1|1|1|4|1727
8 1248223146 1|2|1|1|1|4|111
9 630269717 1|2|1|1|1|4|3343
10 31312601 1|2|1|1|1|4|1929
11 1199112933 1|2|1|1|1|1|310
12 9054001619 1|2|1|1|1|1|29
13 1|2|1|1|1|1|815
14 1|2|1|1|1|1|07
15 1|2|1|1|1|1|613
16 1|2|1|1|1|1|1727
17 1|2|1|1|1|1|18
18 1|2|1|1|1|1|3340
19 1|2|1|1|1|1|1926
20 1|2|1|1|1|10|319
21 1|2|1|1|1|10|218
22 1|2|1|1|1|10|824
23 1|2|1|1|1|10|016
24 1|2|1|1|1|10|622
25 1|2|1|1|1|10|1733
26 1|2|1|1|1|10|117
27 1|2|1|1|1|10|3349
28 1|2|1|1|1|10|1935

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

Thank you for your kind help. Have a great weekend.

Kind Regards,
Moti
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
motilulla said:
Akuini, I am very happy with your code which is working perfectly! It has worked as per request.

This code starts listing from combinations A2. Please, could it be done so that the code can generate the combinations between desired minimum and maximum sums. Apart from that, can i have the sum of each combinations into the column next to it. I want that the combinations can start from column M2 so i can use columns A to K to list my data as shown in the example below:
Click to expand...

Ok, try this:
Note:
You can't get all of the combination. All combination will have 148 columns & since the result start at M2 (column 13) so it will reach column 12 + 148 = 160, which exceeds the limit (256 column). So to get all of the combination just use the previous code, or modify the code to put the result at different column.

Code: 
Sub a1080399e()
'https://www.mrexcel.com/forum/excel-questions/1080399-conditional-combinations-pick-only-1-number-each-column-row.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


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


mn = Range("B1")
mx = Range("B2")

ax = 65000
ReDim vx(1 To ax, 1 To 250)
k = 1
For z = 1 To UBound(vz)
If vz(z, 1) = "" Then Exit For
    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)
                        dSum = vz(z, 1) + va(a, 1) + vb(b, 1) + vc(c, 1) + vd(d, 1) + ve(e, 1) + vf(f, 1)
                        If dSum >= mn And dSum <= mx Then
                        
                            n = n + 1
                            vx(n, k) = vz(z, 1) & "|" & va(a, 1) & "|" & vb(b, 1) & "|" & vc(c, 1) & "|" _
                            & vd(d, 1) & "|" & ve(e, 1) & "|" & vf(f, 1)
                            vx(n, k + 1) = dSum
                        
                            If n Mod 65000 = 0 Then n = 0: k = k + 2
                        
                        End If
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("M2:IV65100").ClearContents
Range("M2").Resize(ax, k + 1) = vx
Columns("M:IV").AutoFit

Application.ScreenUpdating = True


End Sub
 
Upvote 0
Akuini said:
Code: 
Sub a1080399e()
'https://www.mrexcel.com/forum/excel-questions/1080399-conditional-combinations-pick-only-1-number-each-column-row.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


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


mn = Range("B1")
mx = Range("B2")

ax = 65000
ReDim vx(1 To ax, 1 To 250)
k = 1
For z = 1 To UBound(vz)
If vz(z, 1) = "" Then Exit For
    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)
                        dSum = vz(z, 1) + va(a, 1) + vb(b, 1) + vc(c, 1) + vd(d, 1) + ve(e, 1) + vf(f, 1)
                        If dSum >= mn And dSum <= mx Then
                        
                            n = n + 1
                            vx(n, k) = vz(z, 1) & "|" & va(a, 1) & "|" & vb(b, 1) & "|" & vc(c, 1) & "|" _
                            & vd(d, 1) & "|" & ve(e, 1) & "|" & vf(f, 1)
                            vx(n, k + 1) = dSum
                        
                            If n Mod 65000 = 0 Then n = 0: k = k + 2
                        
                        End If
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("M2:IV65100").ClearContents
Range("M2").Resize(ax, k + 1) = vx
Columns("M:IV").AutoFit

Application.ScreenUpdating = True


End Sub
Click to expand...

Akuini said:
Ok, try this:
Note:
You can't get all of the combination. All combination will have 148 columns & since the result start at M2 (column 13) so it will reach column 12 + 148 = 160, which exceeds the limit (256 column). So to get all of the combination just use the previous code, or modify the code to put the result at different column.
Click to expand...
Akuini, hats off to you for solving such a difficult task. Really, I am so grateful to you. As the combination minimum 0 and maximum 162 generates a total of 4,782,969 next to the column combination that is used for each sum. So far all combinations end at column FD. As FD is column number 160-256 = 96 columns that are remianing. So the code is working perfectly.

Thanks a ton for your time and solving all requests as requested.

Good luck and have a great week ahead.

Kind Regards,
Moti :biggrin:
 
Upvote 0
motilulla said:
Akuini, hats off to you for solving such a difficult task. Really, I am so grateful to you. As the combination minimum 0 and maximum 162 generates a total of 4,782,969 next to the column combination that is used for each sum. So far all combinations end at column FD. As FD is column number 160-256 = 96 columns that are remianing. So the code is working perfectly.

Thanks a ton for your time and solving all requests as requested.

Good luck and have a great week ahead.

Kind Regards,
Moti :biggrin:
Click to expand...

Ah, you're right, I guess I was confused between 156 and 256 columns. Glad it worked.:LOL:
 
Upvote 0
Akuini, as you provide a solution under this link to get the combinations sum resume even using single row.</SPAN></SPAN>
https://www.mrexcel.com/forum/excel...an-made-each-sum-post5196986.html#post5196986</SPAN></SPAN>

I adapted the above link code solution with the code you posted under post#12 in this thread using with the following layout to get combinations with sum even used single line </SPAN></SPAN>


Book1
ABCDEFGHIJKLMN
1
2
3Min34Max Of Each Col2214445242
4Max119
5n1n2n3n4n5n6n7CombiSum
6221444522|21|4|4|4|5|242
7
8
9
10
11
12
13
14
Sheet1


Code: 
Sub a1080399e()
'https://www.mrexcel.com/forum/excel-questions/1080399-conditional-combinations-pick-only-1-number-each-column-row.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("D6:J14").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
va = Range(Cells(6, "D"), Cells(rr, "J"))
y = UBound(va, 1)
mn = Range("B3").Value
mx = Range("B4").Value
ax = 65000
ReDim vx(1 To ax, 1 To 250)
k = 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
                        
                        dSum = va(a, 1) + va(b, 2) + va(c, 3) + va(d, 4) _
                                + va(e, 5) + va(f, 6) + va(g, 7)
                        
                        If dSum >= mn And dSum <= mx Then
                        
                            n = n + 1
                            vx(n, k) = va(a, 1) & "|" & va(b, 2) & "|" & va(c, 3) & "|" & va(d, 4) _
                                & "|" & va(e, 5) & "|" & va(f, 6) & "|" & va(g, 7)
                            
                            vx(n, k + 1) = dSum
                        
                            If n Mod 65000 = 0 Then n = 0: k = k + 2
                        
                        End If
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("M6").Resize(ax, k + 1) = vx
'Columns("M:FD").AutoFit
Application.ScreenUpdating = True
 
End Sub

Thank you for your help and Happy Year 2019</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Need help modify to pick any first number of my choice
Replies
6
Views
252
motilulla
M
E
  • Solved
VBA to combine column letter and row number?
Replies
9
Views
150
EssKayKay
E
C
  • Question
Duplicates with different data to combine with VBA
Replies
2
Views
92
chipsworld
C
K
  • Question
Lottery Combinations Filter
Replies
2
Views
523
Fluff
Fluff
K
  • Solved
Find lottery 5_50 3rd price combinations from 12 numbers
Replies
9
Views
538
Kishan
K

Forum statistics

Threads
1,214,575
Messages
6,120,344
Members
448,956
Latest member
Adamsxl

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