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

#### rocker3000

##### New Member
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
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
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

#### mikerickson

##### MrExcel MVP
Are duplicates allowed? Is bbdh a possible solution?

#### rocker3000

##### New Member
Are duplicates allowed? Is bbdh a possible solution?
No duplicates allowed.

#### rocker3000

##### New Member
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
<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
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

#### rocker3000

##### New Member
Genius. Thanks again.

#### motilulla

##### Well-known Member
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
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>