All combinations of k from n from different groups, whose sum is in a specific range

rocker3000

New Member
Joined
Oct 3, 2017
Messages
4
I'll make an example. I have three groups:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">
A
={1,2,3}
B
={4,5}
C
={6,7,8,9}
</code>
I need to find all possible combinations whose sum is between 16 and 20 by picking 2 elements from A, 1 element from B, and 1 elements from C. Each element will have a different letter to index it. For example:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">
A
={a,b,c}
B
={d,e}
C
={f,g,h,i}

</code>Then, a possible combination would be: abdh. This can be shown in separate cells or in a single cell.
It has to be done in Excel, preferably using array formulas but VBA is also fine.

I also need to be able to vary the number of groups, the elements and indexes in each group, how many elements to pick from each group, and the range in which the sums of the combinations have to lie in. So these variables should be inputs and/or I should be able to alter the formula/code to allow for any changes in these.
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Two sheets: One called "Problem" and one called "Solutions":

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Lower Sum</td><td style="text-align: center;;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Upper Sum</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;;">Group Name</td><td style="text-align: center;;">A</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="font-weight: bold;;">Elements From Group</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;;">Group Members</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Problem</p><br /><br />

Code:
Public Sub FindCombos()

Dim setSize As Long
Dim lastCol As Long
Dim thisCol As Long
Dim elCount As Long
Dim i As Long
Dim j As Long
Dim total As Long
Dim solution As String
Dim selectedCells() As Range
Dim rowStart() As Long
Dim setCount() As Long
Dim lastRow As Long
Dim nextRow As Long
Dim allOK As Boolean

' Clear out the solutions
Sheets("Solutions").Cells.ClearContents

' Find the last group and the number of elements we're going to pick
lastCol = Cells(3, Columns.Count).End(xlToLeft).Column
setSize = Application.WorksheetFunction.Sum(Range(Cells(4, 2), Cells(4, lastCol)))

' Set up the arrays
ReDim selectedCells(setSize) As Range
ReDim rowStart(lastCol) As Long
ReDim setCount(lastCol) As Long

' Set the initial selection
i = 1
Set selectedCells(0) = Cells(1, 1)
For thisCol = 2 To lastCol
    For elCount = 1 To Cells(4, thisCol).Value
        Set selectedCells(i) = Cells(4 + elCount, thisCol)
        i = i + 1
    Next elCount
Next thisCol

' Uniquely name each element in the groups
rowStart(2) = 97
For thisCol = 2 To lastCol
    lastRow = Cells(Rows.Count, thisCol).End(xlUp).Row
    setCount(thisCol) = lastRow - 4
    If thisCol < lastCol Then rowStart(thisCol + 1) = rowStart(thisCol) + setCount(thisCol)
Next thisCol

' Next solution row
nextRow = 1

' Keep going until we've exhausted all possibilities
Do While True
    ' Check the current total
    total = 0
    For i = 1 To setSize
        total = total + selectedCells(i).Value
    Next i
    
    ' Total is in range?
    If total >= Cells(1, 2).Value And total <= Cells(2, 2).Value Then
        ' Generate the solution
        solution = ""
        For i = 1 To setSize
            solution = solution & Chr$(rowStart(selectedCells(i).Column) + selectedCells(i).Row - 5)
        Next i
        
        ' Print the solution on the solution sheet
        Sheets("Solutions").Cells(nextRow, 1).Value = solution
        nextRow = nextRow + 1
    End If
    
    ' Tick over to the next selection
    i = setSize
    Do While True
        ' Move the cell down
        Set selectedCells(i) = selectedCells(i).Offset(1, 0)
        
        ' OK?
        If selectedCells(i).Value = "" Then
            ' Move this back to the top
            Set selectedCells(i) = Cells(5, selectedCells(i).Column)
            
            ' Move to the previous cell and move that
            i = i - 1
        Else
            allOK = True
            ' Adjust all other cells
            If i < setSize Then
                For j = i + 1 To setSize
                    If selectedCells(j).Column = selectedCells(i).Column Then
                        Set selectedCells(j) = selectedCells(j - 1).Offset(1, 0)
                        If selectedCells(j).Value = "" Then
                            i = i - 1
                            allOK = False
                            Exit For
                        End If
                    End If
                Next j
            End If
            
            ' Column exhausted
            If allOK Or i = 0 Then Exit Do
            
            ' Reset column?
            If selectedCells(i).Column <> selectedCells(i + 1).Column Then
                Set selectedCells(i + 1) = Cells(5, selectedCells(i + 1).Column)
                For j = i + 2 To setSize
                    If selectedCells(j).Column = selectedCells(j - 1).Column Then
                        Set selectedCells(j) = selectedCells(j - 1).Offset(1, 0)
                    End If
                Next j
            End If
        End If
        
        ' Done?
        If i = 0 Then Exit Do
    Loop
    
    ' Finished?
    If i = 0 Then Exit Do
Loop

End Sub
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">abdi</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">abeh</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">abei</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">acdh</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">acdi</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">aceg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">aceh</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">acei</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">bcdg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">bcdh</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">bcdi</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">bcef</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">bceg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">bceh</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">bcei</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Solutions</p><br /><br />

WBD
 

rocker3000

New Member
Joined
Oct 3, 2017
Messages
4
Great solution. Thanks!

If I wanted to have different indexes for each group? Say:

A={a,b,c}
B={1,2}
C={A,B,C,D}

So a solution could be ab2C.
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Lower Sum</td><td style="text-align: center;;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Upper Sum</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;;">Group Start Character</td><td style="text-align: center;;">a</td><td style="text-align: center;;">1</td><td style="text-align: center;;">A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="font-weight: bold;;">Elements From Group</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;;">Group Members</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Problem</p><br /><br />

Code:
Public Sub FindCombos()

Dim setSize As Long
Dim lastCol As Long
Dim thisCol As Long
Dim elCount As Long
Dim i As Long
Dim j As Long
Dim total As Long
Dim solution As String
Dim selectedCells() As Range
Dim rowStart() As Long
Dim setCount() As Long
Dim lastRow As Long
Dim nextRow As Long
Dim allOK As Boolean

' Clear out the solutions
Sheets("Solutions").Cells.ClearContents

' Find the last group and the number of elements we're going to pick
lastCol = Cells(3, Columns.Count).End(xlToLeft).Column
setSize = Application.WorksheetFunction.Sum(Range(Cells(4, 2), Cells(4, lastCol)))

' Set up the arrays
ReDim selectedCells(setSize) As Range
ReDim rowStart(lastCol) As Long
ReDim setCount(lastCol) As Long

' Set the initial selection
i = 1
Set selectedCells(0) = Cells(1, 1)
For thisCol = 2 To lastCol
    For elCount = 1 To Cells(4, thisCol).Value
        Set selectedCells(i) = Cells(4 + elCount, thisCol)
        i = i + 1
    Next elCount
Next thisCol

' Uniquely name each element in the groups
For thisCol = 2 To lastCol
    lastRow = Cells(Rows.Count, thisCol).End(xlUp).Row
    setCount(thisCol) = lastRow - 4
    rowStart(thisCol) = Asc(Cells(3, thisCol))
Next thisCol

' Next solution row
nextRow = 1

' Keep going until we've exhausted all possibilities
Do While True
    ' Check the current total
    total = 0
    For i = 1 To setSize
        total = total + selectedCells(i).Value
    Next i
    
    ' Total is in range?
    If total >= Cells(1, 2).Value And total <= Cells(2, 2).Value Then
        ' Generate the solution
        solution = ""
        For i = 1 To setSize
            solution = solution & Chr$(rowStart(selectedCells(i).Column) + selectedCells(i).Row - 5)
        Next i
        
        ' Print the solution on the solution sheet
        Sheets("Solutions").Cells(nextRow, 1).Value = solution
        nextRow = nextRow + 1
    End If
    
    ' Tick over to the next selection
    i = setSize
    Do While True
        ' Move the cell down
        Set selectedCells(i) = selectedCells(i).Offset(1, 0)
        
        ' OK?
        If selectedCells(i).Value = "" Then
            ' Move this back to the top
            Set selectedCells(i) = Cells(5, selectedCells(i).Column)
            
            ' Move to the previous cell and move that
            i = i - 1
        Else
            allOK = True
            ' Adjust all other cells
            If i < setSize Then
                For j = i + 1 To setSize
                    If selectedCells(j).Column = selectedCells(i).Column Then
                        Set selectedCells(j) = selectedCells(j - 1).Offset(1, 0)
                        If selectedCells(j).Value = "" Then
                            i = i - 1
                            allOK = False
                            Exit For
                        End If
                    End If
                Next j
            End If
            
            ' Column exhausted
            If allOK Or i = 0 Then Exit Do
            
            ' Reset column?
            If selectedCells(i).Column <> selectedCells(i + 1).Column Then
                Set selectedCells(i + 1) = Cells(5, selectedCells(i + 1).Column)
                For j = i + 2 To setSize
                    If selectedCells(j).Column = selectedCells(j - 1).Column Then
                        Set selectedCells(j) = selectedCells(j - 1).Offset(1, 0)
                    End If
                Next j
            End If
        End If
        
        ' Done?
        If i = 0 Then Exit Do
    Loop
    
    ' Finished?
    If i = 0 Then Exit Do
Loop

End Sub
Changed row 3 to indicate the first character of the group.

WBD
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
Hello WBD, I open thread yesterday under this link </SPAN></SPAN>
https://www.mrexcel.com/forum/excel-questions/1113184-lottery-combinations-post5361432.html#post5361432
</SPAN></SPAN>
Later searching in the site Goggle and in the MrExcel forums I come across to your thread I guess post#2 is a solution of my request. But I need numbers to be real and result must be split in the 5 columns instead alphabetical result in the one column.
</SPAN></SPAN>

Does is it possible? Please can you take a look?
</SPAN></SPAN>

Thank you
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 
Last edited:

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
Hello WBD, I open thread yesterday under this link </SPAN></SPAN>
https://www.mrexcel.com/forum/excel-questions/1113184-lottery-combinations-post5361432.html#post5361432
</SPAN></SPAN>
Later searching in the site Goggle and in the MrExcel forums I come across to your thread I guess post#2 is a solution of my request. But I need numbers to be real and result must be split in the 5 columns instead alphabetical result in the one column.
</SPAN></SPAN>

Does is it possible? Please can you take a look?
</SPAN></SPAN>

Thank you
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
Any idea Please?
 

Forum statistics

Threads
1,084,878
Messages
5,380,420
Members
401,676
Latest member
BobH

Some videos you may like

This Week's Hot Topics

Top