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
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