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

motilulla · Dec 10, 2018

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

></o

>
<o

></o

>
Hello,<o

></o

>
<o

></o

>
I have some numbers in column D:J (each column has variable quantity of numbers in it) <o

></o

>
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

></o

>
In the column E, G, H, I, J there are 2 numbers and in column F there are 4 numbers<o

></o

>
<o

></o

>
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

></o

>
<o

></o

>
Some example rows are Conditional combination's listed below in the column N:U <o

></o

>

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

2

3

n1

n2

n3

n4

n5

n6

n7

R1

R2

R3

R4

R5

R6

R7

Sum

4

2

1

0

8

4

2

1

0

8

4

2

19

5

6

2

6

23

1

8

2

6

1

0

8

4

2

23

6

3

2

1

6

8

4

2

25

7

9

2

1

0

23

4

2

34

8

2

1

0

8

1

2

16

9

2

1

0

8

4

8

25

10

2

6

2

0

8

4

2

24

11

2

6

1

6

8

4

2

29

12

2

6

1

0

23

4

2

38

13

2

6

1

0

8

4

8

29

14

2

6

8

4

2

26

15

2

0

23

4

2

35

16

2

0

8

1

2

17

2

0

8

4

8

26

Sheet10

<o

></o

>
<o

></o

>
Thank you all<o

></o

>
<o

></o

>
Excel 2000<o

></o

>
Regards,
Moti

motilulla · Dec 11, 2018

Hello, to make simpler my request there are couples of things

1- Cell D4 number 2 is a number, which has to be used in each row with all the combinations, rest 6 numbers need to combine with it to generate all combinations
2- in the each row of combinations should not be taken 2 or more numbers from any of the 6 columns say for example 2-2-6-1-0-8-4, (2-6 can not be taken from E4 & E5) in the same row to create any combinations

Hope this makes bit clearer

Regards,
Moti

Akuini · Dec 11, 2018

motilulla said:
Hello, to make simpler my request there are couples of things

1- Cell D4 number 2 is a number, which has to be used in each row with all the combinations, rest 6 numbers need to combine with it to generate all combinations
2- in the each row of combinations should not be taken 2 or more numbers from any of the 6 columns say for example 2-2-6-1-0-8-4, (2-6 can not be taken from E4 & E5) in the same row to create any combinations

Hope this makes bit clearer

Regards,
Moti

Try this:

Code:

Sub a1080399a()
'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

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

ReDim vx(1 To 1000, 1 To 7)

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) = 2
                        vx(n, 2) = va(a, 1)
                        vx(n, 3) = vb(b, 1)
                        vx(n, 4) = vc(c, 1)
                        vx(n, 5) = vd(d, 1)
                        vx(n, 6) = ve(e, 1)
                        vx(n, 7) = vf(f, 1)

                    Next
                Next
            Next
        Next
    Next
Next

Range("N4").Resize(n, 7) = vx

End Sub

motilulla · Dec 11, 2018

Akuini said:

Try this:

Code:

Sub a1080399a()
'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

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

ReDim vx(1 To 1000, 1 To 7)

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) = 2
                        vx(n, 2) = va(a, 1)
                        vx(n, 3) = vb(b, 1)
                        vx(n, 4) = vc(c, 1)
                        vx(n, 5) = vd(d, 1)
                        vx(n, 6) = ve(e, 1)
                        vx(n, 7) = vf(f, 1)

                    Next
                Next
            Next
        Next
    Next
Next

Range("N4").Resize(n, 7) = vx

End Sub

Akuini, VBA work superb!!

I imagine this were difficult task.

I did mention 1st column D I would be use only the 1 number, please is it possible for you to alter the code if I use either 1 or 2 numbers in the D? 

Thank you so much for your kind help. 

Have a nice week ahead 

Kind Regards
Moti

Akuini · Dec 11, 2018

motilulla said:
Akuini, VBA work superb!! I imagine this were difficult task.

I did mention 1st column D I would be use only the 1 number, please is it possible for you to alter the code if I use either 1 or 2 numbers in the D?

Thank you so much for your kind help.

Have a nice week ahead

Kind Regards
Moti

Ok, try this:

Code:

Sub a1080399b()
'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, vz

vz = Range("D4:D5")
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))

ReDim vx(1 To 1000, 1 To 7)

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)
                        
                            n = n + 1
                            vx(n, 1) = vz(z, 1)
                            vx(n, 2) = va(a, 1)
                            vx(n, 3) = vb(b, 1)
                            vx(n, 4) = vc(c, 1)
                            vx(n, 5) = vd(d, 1)
                            vx(n, 6) = ve(e, 1)
                            vx(n, 7) = vf(f, 1)
    
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("N4").Resize(n, 7) = vx

End Sub

motilulla · Dec 11, 2018

Akuini said:

Ok, try this:

Code:

Sub a1080399b()
'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, vz

vz = Range("D4:D5")
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))

ReDim vx(1 To 1000, 1 To 7)

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)
                        
                            n = n + 1
                            vx(n, 1) = vz(z, 1)
                            vx(n, 2) = va(a, 1)
                            vx(n, 3) = vb(b, 1)
                            vx(n, 4) = vc(c, 1)
                            vx(n, 5) = vd(d, 1)
                            vx(n, 6) = ve(e, 1)
                            vx(n, 7) = vf(f, 1)
    
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("N4").Resize(n, 7) = vx

End Sub

Akuini, Thank you so much for modifying the code as per my second request. It is working flawless!!

Kind Regards
Moti

motilulla · Dec 12, 2018

motilulla said:
Akuini, Thank you so much for modifying the code as per my second request. It is working flawless!!

Kind Regards
Moti

Akuini, I understand it is irritating for you or any programmer is doing voluntarily to solve ones problems, in this thread you solved twice. After getting twice solutions by you and working on it I find if this finally could be the done the way which I did not thought till having code by you and getting the result of my analysis.

Before the code I thought that having the 9 numbers in each of the 7 columns, it could be approximately 20000 to 25000 rows combinations. I modified 2 thing in code from post#5, 1- change the range Range("D4:D5") to Range("D4:D12") and ReDim vx(1 To 1000, 1 To 7) to ReDim vx(1 To 100000, 1 To 7) and filled 9 number in the each column in cells D4:J12.

After changes when I run the code it stop at the line--> vx(n, 1) = vz(z, 1) I imagine this is because there are more than 65536 row which my excel 2000 does not permit. It is not a code problem.

There could be the 2 solutions one code continuing writing in the next columns or other options restrict the sum.

Akuini, this is my third request in the same thread. Please will it be possible for you to recoding to get result from the following data. Minimum sum will be 0 and max sum can be 162, as using all these number there could be more than 65000 combinations. 

It is just my thoughts if the code generates all combinations rows has the fix sum example 36 it would be limited or if possible it could generate in the sum between range say for example 28 to 38. And these min & max sum values can be filled in cell B1 & B2 before the code execute. I think will be, always less then 65000 rows of combinations

Here is the new example data

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Min

28

Max Each Col

27

12

9

42

23

16

33

162

2

Max

38

3

n1

n2

n3

n4

n5

n6

n7

R1

R2

R3

R4

R5

R6

R7

Sum

4

1

2

1

4

3

5

0

6

2

0

8

1

2

6

2

12

4

6

18

10

8

7

27

5

7

42

3

6

0

8

12

4

8

2

23

14

6

9

6

3

0

26

9

7

17

10

3

1

3

12

6

0

1

11

9

11

2

9

33

12

9

0

5

40

0

16

19

13

14

15

16

17

Sheet10

Sorry for the troubles 

Thank you

Kind Regards
Moti

Akuini · Dec 12, 2018

motilulla said:
Akuini, this is my third request in the same thread. Please will it be possible for you to recoding to get result from the following data. Minimum sum will be 0 and max sum can be 162, as using all these number there could be more than 65000 combinations.

It is just my thoughts if the code generates all combinations rows has the fix sum example 36 it would be limited or if possible it could generate in the sum between range say for example 28 to 38. And these min & max sum values can be filled in cell B1 & B2 before the code execute. I think will be, always less then 65000 rows of combinations

Moti

Ok, try this:
The macro will stop if the result reach 65000 rows. Try min = max first, see what you get.

Code:

Sub a1080399c()
'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

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

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, 1) = vz(z, 1)
                            vx(n, 2) = va(a, 1)
                            vx(n, 3) = vb(b, 1)
                            vx(n, 4) = vc(c, 1)
                            vx(n, 5) = vd(d, 1)
                            vx(n, 6) = ve(e, 1)
                            vx(n, 7) = vf(f, 1)
                        
                        If n >= ax Then
                        MsgBox "The result is more than " & ax & " rows. This macro will stop"
                        Exit Sub
                        End If
                        
                        End If
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("N4").Resize(n, 7) = vx

End Sub

Using your example, all combination will reach 9^7 = 4,782,969 rows & 7 columns.
A worksheet in Excel 2000 is limited to 65,536 rows & 256 column. If you want to get all the 4,782,969 rows x 7 columns, there's a workaround. We can put the result per 65000 rows, and maybe we can use about 250 column & we may need 3 worksheets.
Let me know if this approach suit you.

motilulla · Dec 13, 2018

Akuini said:

Ok, try this:
The macro will stop if the result reach 65000 rows. Try min = max first, see what you get.

Code:

Sub a1080399c()
'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

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

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, 1) = vz(z, 1)
                            vx(n, 2) = va(a, 1)
                            vx(n, 3) = vb(b, 1)
                            vx(n, 4) = vc(c, 1)
                            vx(n, 5) = vd(d, 1)
                            vx(n, 6) = ve(e, 1)
                            vx(n, 7) = vf(f, 1)
                        
                        If n >= ax Then
                        MsgBox "The result is more than " & ax & " rows. This macro will stop"
                        Exit Sub
                        End If
                        
                        End If
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Range("N4").Resize(n, 7) = vx

End Sub

Akuini,

ray: I heartily appreciate a lot your help for fulfilling my 3rd request. Thank you so much.

Akuini said:
Using your example, all combination will reach 9^7 = 4,782,969 rows & 7 columns.

I did not imagined there could be so much combination. As per example Post#7, if the min sum is = 0 and max sum can be = 162 is there any method to list out how much combinations can be made with each sum 0 to 162? 

I tried to run the code with min = 34 and max = 34 got message " The result is more than 65000 rows. This macro will stop" this mean there are more than 65000 with sum 34 how much don't know, used min & max sum 33 it generated 64357 perfectly

Akuini said:
A worksheet in Excel 2000 is limited to 65,536 rows & 256 column. If you want to get all the 4,782,969 rows x 7 columns, there's a workaround. We can put the result per 65000 rows, and maybe we can use about 250 column & we may need 3 worksheets.
Let me know if this approach suit you.

Akuini, instead of using 7 columns for each row, would it be possible to create combinations in the single column separated by vertical bar like this 0|0|0|0|0|0|0. If so 4,782,969 / 65000 = 73.58 nearly 74-columns and if one space is used in-betweens of each columns total column will be 148-columns and all can be generated in a single sheet. It is just my thought. If This is possible than I imagine can be used min = 34 & max = 35 to generate sum with 2 sums or using min = 0 & max = 160 to generate all at once in a single sheet

Please give your opinion. Programmatically may be it is possible or not? 

Kind Regards
Moti

Akuini · Dec 14, 2018

motilulla said:
Akuini, instead of using 7 columns for each row, would it be possible to create combinations in the single column separated by vertical bar like this 0|0|0|0|0|0|0. If so 4,782,969 / 65000 = 73.58 nearly 74-columns and if one space is used in-betweens of each columns total column will be 148-columns and all can be generated in a single sheet. It is just my thought. If This is possible than I imagine can be used min = 34 & max = 35 to generate sum with 2 sums or using min = 0 & max = 160 to generate all at once in a single sheet

Please give your opinion. Programmatically may be it is possible or not?

Kind Regards
Moti

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

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

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

Akuini

Well-known Member

motilulla

Well-known Member

Akuini

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

Akuini

Well-known Member

motilulla

Well-known Member

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

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

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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