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

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
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:

[TABLE="width: 945"]
<colgroup><col span="2"><col><col><col span="7"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Min[/TD]
[TD]34[/TD]
[TD]Max Each Col[/TD]
[TD]27[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]42[/TD]
[TD]23[/TD]
[TD]16[/TD]
[TD]33[/TD]
[TD]162[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sum[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Max[/TD]
[TD]34[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|3[/TD]
[TD="align: right"]13[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]n1[/TD]
[TD]n2[/TD]
[TD]n3[/TD]
[TD]n4[/TD]
[TD]n5[/TD]
[TD]n6[/TD]
[TD]n7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|2[/TD]
[TD="align: right"]12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|8[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|0[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]18[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|6[/TD]
[TD="align: right"]16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]42[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|17[/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]23[/TD]
[TD]14[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|1[/TD]
[TD="align: right"]11[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|33[/TD]
[TD="align: right"]43[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|4|19[/TD]
[TD="align: right"]29[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]33[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|3[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|2[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|8[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|0[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|6[/TD]
[TD="align: right"]13[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|17[/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|1[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|33[/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|1|19[/TD]
[TD="align: right"]26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|3[/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|2[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|8[/TD]
[TD="align: right"]24[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|0[/TD]
[TD="align: right"]16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|6[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|17[/TD]
[TD="align: right"]33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|1[/TD]
[TD="align: right"]17[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|33[/TD]
[TD="align: right"]49[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1|2|1|1|1|10|19[/TD]
[TD="align: right"]35[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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

Kind Regards,
Moti
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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 :-D
 
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 :-D
Click to expand...

Ah, you're right, I guess I was confused between 156 and 256 columns. Glad it worked.:laugh:
 
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

W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
349
waffle
W
J
  • Question Question
Conditional formatting of single column of pivot table
Replies
8
Views
233
Peter_SSs
Peter_SSs
Excelwhatsthat
  • Question Question
Scroll enabled to minimise table view...but need conditional formatting
Replies
0
Views
79
Excelwhatsthat
Excelwhatsthat
S
  • Question Question
Excel Formula to build out Combinations/Permutations of a series of numbers
Replies
1
Views
197
Eric W
Eric W
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
406
Fluff
Fluff

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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