# VBA get target sum from the grid of 7*9

#### motilulla

##### Well-known Member
Hello,

I need a macro that allow me to create target sum from the grid of 7 columns and 9 rows...using technique picking a one number from each columns and making all possible targets sum can be made in the "as per specified sum target in the column S"

Example below shows only one set of sum 17 (in the columns L:R) but I hope could be made much more than ones...

Thank you all

Excel 2000
Regards,
Moti

Last edited:

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Eric W

##### MrExcel MVP
It is similar to that, but this has the added constraint that you must take one value from each column. There are therefore 9^7 possible combinations, or 4,782,969. A big number, but not too bad for a reasonably fast computer. One big similarity to the challenge is that the number of results is really far too big to really do anything with. This particular problem has 93,063 solutions. Here's the code:

Rich (BB code):
``````Sub Sub97()
Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long
Dim s As Long, sc As String, target As Long, Output(), x As Variant, y As Variant

Set Dict = CreateObject("Scripting.Dictionary")

target = Range("S6").Value
grid = Range("C6:I14")
r = UBound(grid, 1)
c = UBound(grid, 2)
ReDim ix(1 To c)

For i = 1 To c
ix(i) = 1
Next i

ChkAgain:
s = 0
sc = ""
For i = 1 To c
s = s + grid(ix(i), i)
sc = sc & grid(ix(i), i) & IIf(i < c, "|", "")
Next i
If s = target Then Dict(sc) = 1

For i = 1 To c
ix(i) = ix(i) + 1
If ix(i) <= r Then GoTo ChkAgain:
ix(i) = 1
Next i

ReDim Output(1 To Dict.Count, 1 To c)
r = 1
For Each x In Dict
y = Split(x, "|")
For i = 1 To c
Output(r, i) = y(i - 1)
Next i
r = r + 1
Next x

Range("L6").Resize(Dict.Count, c) = Output

End Sub``````
It pulls the table from C6:I14. It pulls the target value from S6. (The red values.) But you can change them in the code, and the rest will adapt to the new sizes. This ran in about 30 seconds on my PC.

Last edited:

#### motilulla

##### Well-known Member
It is similar to that, but this has the added constraint that you must take one value from each column. There are therefore 9^7 possible combinations, or 4,782,969. A big number, but not too bad for a reasonably fast computer. One big similarity to the challenge is that the number of results is really far too big to really do anything with. This particular problem has 93,063 solutions. Here's the code:

Rich (BB code):
``````Sub Sub97()
Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long
Dim s As Long, sc As String, target As Long, Output(), x As Variant, y As Variant

Set Dict = CreateObject("Scripting.Dictionary")

target = Range("S6").Value
grid = Range("C6:I14")
r = UBound(grid, 1)
c = UBound(grid, 2)
ReDim ix(1 To c)

For i = 1 To c
ix(i) = 1
Next i

ChkAgain:
s = 0
sc = ""
For i = 1 To c
s = s + grid(ix(i), i)
sc = sc & grid(ix(i), i) & IIf(i < c, "|", "")
Next i
If s = target Then Dict(sc) = 1

For i = 1 To c
ix(i) = ix(i) + 1
If ix(i) <= r Then GoTo ChkAgain:
ix(i) = 1
Next i

ReDim Output(1 To Dict.Count, 1 To c)
r = 1
For Each x In Dict
y = Split(x, "|")
For i = 1 To c
Output(r, i) = y(i - 1)
Next i
r = r + 1
Next x

Range("L6").Resize(Dict.Count, c) = Output

End Sub``````
It pulls the table from C6:I14. It pulls the target value from S6. (The red values.) But you can change them in the code, and the rest will adapt to the new sizes. This ran in about 30 seconds on my PC.
Eric, thank you so much for the code it works fine when set are less than 65531 rows in other case it highlights following line "Range("L6").Resize(Dict.Count, c) = Output" and give the error 1004, I did test with sum -27 it create 32928 set perfectly</SPAN></SPAN>

As with the sum 17 there is 93,063 sets can't complete it due to version 2000 row limits does it has some solution for it. Please advice.
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

#### Eric W

##### MrExcel MVP

What do you want to do with it? There's really no reason to display all 93,063 combinations. That's far too many to manually examine. If you have some way of sifting through the list and picking out some, let me know and I can apply it as part of the macro. If you just want the count, I can display the count and the first 65,000 combinations. If you have some strange reason for wanting to actually show them all, I could put 65K in L:R, 65K in T:Z, 65K in AB:AH, etc. Excel 2000 has 256 columns, so that would give you up to almost 2 million combinations. But that's overkill.

Last edited:

#### motilulla

##### Well-known Member
Eric, thank you very much for your response
What do you want to do with it? There's really no reason to display all 93,063 combinations.
I totally agree with you I did not new it could be so much but now problem is that I cannot list any. And get the error 1004 so far I cannot examine nothing. Also as you said there could be possible combinations 4,782,969 so do I can know? How much could be with each sum as for example you test with sum 17=93,063, and I did test with sum -27 it create 32928 set perfectly.

That's far too many to manually examine. If you have some way of sifting through the list and picking out some, let me know and I can apply it as part of the macro. If you just want the count, I can display the count and the first 65,000 combinations.
Really as never generated non-to analysis no idea.

If you have some strange reason for wanting to actually show them all, I could put 65K in L:R, 65K in T:Z, 65K in AB:AH, etc. Excel 2000 has 256 columns, so that would give you up to almost 2 million combinations. But that's overkill.
</SPAN></SPAN>In this case I think would be better to have a list total combinations with each sum could be...and than pick one sum to generate all with selected sum if are more than 65000 than go to as you suggest. "You could put 65K in L:R, 65K in T:Z, 65K in AB:AH, etc." </SPAN></SPAN>

I imagine it needs a lot of work to solve in this way. My request is to you if possible please can you do it for me. No problem of time when you can
</SPAN></SPAN>thank you

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

#### Eric W

##### MrExcel MVP

Try:
Rich (BB code):
``````Sub Sub97()
Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long, outrange as Range
Dim s As Long, sc As String, target As Long, Output(), x As Variant, y As Variant

Set Dict = CreateObject("Scripting.Dictionary")

target = Range("S6").Value
grid = Range("C6:I14")
Set outrange = Range("L6")

r = UBound(grid, 1)
c = UBound(grid, 2)
ReDim ix(1 To c)

For i = 1 To c
ix(i) = 1
Next i

ChkAgain:
s = 0
sc = ""
For i = 1 To c
s = s + grid(ix(i), i)
sc = sc & grid(ix(i), i) & "|"
Next i
If s = target Then Dict(sc) = 1

For i = 1 To c
ix(i) = ix(i) + 1
If ix(i) <= r Then GoTo ChkAgain:
ix(i) = 1
Next i

r = 0
ReDim Output(1 To 65000, 1 To c)
For Each x In Dict
y = Split(x, "|")
r = r + 1
For i = 1 To c
Output(r, i) = y(i - 1)
Next i
If r = 65000 Then
outrange.Offset(-1).Resize(1, c) = Range("C5:I5").Value
outrange.Resize(65000, c) = Output
ReDim Output(1 To 65000, 1 To c)
r = 0
Set outrange = outrange.Offset(, c + 1)
End If
Next x

If r > 0 Then
outrange.Offset(-1).Resize(1, c) = Range("C5:I5").Value
outrange.Resize(65000, c) = Output
End If

End Sub``````
Values in red would need to be changed if you ever rearrange your sheet.

#### motilulla

##### Well-known Member
Try:
Rich (BB code):
``````Sub Sub97()
Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long, outrange as Range
Dim s As Long, sc As String, target As Long, Output(), x As Variant, y As Variant

Set Dict = CreateObject("Scripting.Dictionary")

target = Range("S6").Value
grid = Range("C6:I14")
Set outrange = Range("L6")

r = UBound(grid, 1)
c = UBound(grid, 2)
ReDim ix(1 To c)

For i = 1 To c
ix(i) = 1
Next i

ChkAgain:
s = 0
sc = ""
For i = 1 To c
s = s + grid(ix(i), i)
sc = sc & grid(ix(i), i) & "|"
Next i
If s = target Then Dict(sc) = 1

For i = 1 To c
ix(i) = ix(i) + 1
If ix(i) <= r Then GoTo ChkAgain:
ix(i) = 1
Next i

r = 0
ReDim Output(1 To 65000, 1 To c)
For Each x In Dict
y = Split(x, "|")
r = r + 1
For i = 1 To c
Output(r, i) = y(i - 1)
Next i
If r = 65000 Then
outrange.Offset(-1).Resize(1, c) = Range("C5:I5").Value
outrange.Resize(65000, c) = Output
ReDim Output(1 To 65000, 1 To c)
r = 0
Set outrange = outrange.Offset(, c + 1)
End If
Next x

If r > 0 Then
outrange.Offset(-1).Resize(1, c) = Range("C5:I5").Value
outrange.Resize(65000, c) = Output
End If

End Sub``````
Values in red would need to be changed if you ever rearrange your sheet.
Wow, I appreciate your kind help Eric . It worked like an appeal. Absolutely Solved!</SPAN></SPAN>

Good Luck
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

Eric, I have one more question how can I list of all (min+sum, max+sum), (min-sum and min-sum) then generate one by one with all set to know how much could be total with each sum = sum must be 4.782.969
</SPAN></SPAN>

Max+sum = 56 with this there is only 1 set
</SPAN></SPAN>
Min+sum = -113 with this there is only 1 set
</SPAN></SPAN>

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">n7</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-4 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-6 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">-2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-4 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-5 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-2 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-10 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-32 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-1 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-16 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">6 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">7 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-16 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-9 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">-2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-18 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-32 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-3 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Min+ sum</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Max+ Sum</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">56</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Min- Sum</td><td style="font-weight: bold;text-align: center;;">-4</td><td style="font-weight: bold;text-align: center;;">-18</td><td style="font-weight: bold;text-align: center;;">-32</td><td style="font-weight: bold;text-align: center;;">-32</td><td style="font-weight: bold;text-align: center;;">-10</td><td style="font-weight: bold;text-align: center;;">-8</td><td style="font-weight: bold;text-align: center;;">-9</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">-113</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Max-Sum</td><td style="font-weight: bold;text-align: center;;">-2</td><td style="font-weight: bold;text-align: center;;">-2</td><td style="font-weight: bold;text-align: center;;">-16</td><td style="font-weight: bold;text-align: center;;">-2</td><td style="font-weight: bold;text-align: center;;">-1</td><td style="font-weight: bold;text-align: center;;">-2</td><td style="font-weight: bold;text-align: center;;">-2</td><td style="font-weight: bold;text-align: center;;">-27</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

Last edited:

#### Eric W

##### MrExcel MVP
I'm not entirely sure what you want, but try:

Rich (BB code):
``````Sub Sub97c()
Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long, outrange As Range
Dim s As Long, sc As String, target As Long, output(), x As Variant, y As Variant

Set Dict = CreateObject("Scripting.Dictionary")

target = Range("S6").Value
grid = Range("C6:I14")
Set outrange = Range("B25")

r = UBound(grid, 1)
c = UBound(grid, 2)
ReDim ix(1 To c)

For i = -113 To 56
Dict(i) = 0
Next i
For i = 1 To c
ix(i) = 1
Next i

ChkAgain:
s = 0
sc = ""
For i = 1 To c
s = s + grid(ix(i), i)
sc = sc & grid(ix(i), i) & "|"
Next i
Dict(s) = Dict(s) + 1

For i = 1 To c
ix(i) = ix(i) + 1
If ix(i) <= r Then GoTo ChkAgain:
ix(i) = 1
Next i

outrange.Resize(1, 2) = Array("Value", "Number of ways to achieve value")
outrange.Offset(1).Resize(Dict.Count) = WorksheetFunction.Transpose(Dict.keys)
outrange.Offset(1, 1).Resize(Dict.Count) = WorksheetFunction.Transpose(Dict.items)

End Sub``````
Change the B25 to where you want the output table to go.

#### motilulla

##### Well-known Member
I'm not entirely sure what you want, but try:

Rich (BB code):
``````Sub Sub97c()
Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long, outrange As Range
Dim s As Long, sc As String, target As Long, output(), x As Variant, y As Variant

Set Dict = CreateObject("Scripting.Dictionary")

target = Range("S6").Value
grid = Range("C6:I14")
Set outrange = Range("B25")

r = UBound(grid, 1)
c = UBound(grid, 2)
ReDim ix(1 To c)

For i = -113 To 56
Dict(i) = 0
Next i
For i = 1 To c
ix(i) = 1
Next i

ChkAgain:
s = 0
sc = ""
For i = 1 To c
s = s + grid(ix(i), i)
sc = sc & grid(ix(i), i) & "|"
Next i
Dict(s) = Dict(s) + 1

For i = 1 To c
ix(i) = ix(i) + 1
If ix(i) <= r Then GoTo ChkAgain:
ix(i) = 1
Next i

outrange.Resize(1, 2) = Array("Value", "Number of ways to achieve value")
outrange.Offset(1).Resize(Dict.Count) = WorksheetFunction.Transpose(Dict.keys)
outrange.Offset(1, 1).Resize(Dict.Count) = WorksheetFunction.Transpose(Dict.items)

End Sub``````
Change the B25 to where you want the output table to go.
Eric, I just ask for list but have given an entire solution with all the ended combinations. Surprised also within a 15 min that is unbelievable. I am very great full to you Eric it has been time saver solution for me. </SPAN></SPAN>

Thank you for solving complete request. Eric I am
</SPAN></SPAN>

Have a good day and Good Luck
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

Replies
0
Views
124
Replies
12
Views
405
Replies
4
Views
569
Replies
13
Views
1K
Replies
3
Views
334

1,129,463
Messages
5,636,427
Members
416,918
Latest member
twc2c

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

### Which adblocker are you using?

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

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