Finding pieces of a value in a table

[root]

Suppose I have a set of data below. I know that that a combination o
some values in dataset make up a value of 252. What is the mos
efficient formula to find and isolate these values?
34
3
34
34
33
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
5

 

[Guest]

I can't answer your original question, but you don't need to post the
question multiple times. If someone can answer it they will. If they
can't, it will remain unanswered.

 

[Maistrye]

Suppose I have a set of data below. I know that that a combination of
some values in dataset make up a value of 252. What is the most
efficient formula to find and isolate these values?
34
3
34
34
33
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

You might want to look up "SUBSET SUM" on the internet. If you know
what it means, this problem is NP-C. Basically, that means that in
general you won't be able to find a solution very fast.

If you only have a few numbers, like above, you can probably figure it
out by just trying all possibilities until you find one that works.
It's going to be harder to do in excel as with 22 values, there are
2^22 or about 4.2 million combinations. Your options are probably
limited to VB, the Solver, or guess and test. IMO, VB is probably the
better choice to run through all the possibilities and return one that
works. (You might be able to guess and test with these numbers
though... just my opinion.)

Scott

 

[root]

Ok, thankx for your help.

System issue caused the post to replicate, I deleted the duplicated

 

[Maistrye]

Here's some code that will give you an answer for this.

It assumes your values are in A1:A22 and that you want the answers in
B1:B22. (in Sheet1)

Scott

---------
Option Explicit

Const N = 22
Const SolveVal = 252

Dim X(N - 1) As Long
Dim Answer(N - 1) As Long

Function FindAnswer(Val As Long, Sum As Long) As Boolean
If (Sum = SolveVal) Then
FindAnswer = True
Exit Function
ElseIf (Val = N Or Sum > SolveVal) Then
FindAnswer = False
Exit Function
End If

Answer(Val) = 1
If (FindAnswer(Val + 1, Sum + X(Val)) = True) Then
FindAnswer = True
Exit Function
End If

Answer(Val) = 0
If (FindAnswer(Val + 1, Sum) = True) Then
FindAnswer = True
Exit Function
End If

FindAnswer = False
End Function

Sub SubsetSum()
Dim W As Worksheet
Dim i As Long

Set W = Worksheets("Sheet1")

For i = 1 To N
X(i - 1) = W.Cells(i, 1)
Next i

If (FindAnswer(0, 0) = True) Then
For i = 1 To N
W.Cells(i, 2) = Answer(i - 1)
Next i
Else
MsgBox ("No solution")
End If
End Sub

 

[root]

Scott,
Thanx for the code. It works. Would this give me the first solution it
finds? What if there are multiple solutions?

 

[Maistrye]

Scott,
Thanx for the code. It works. Would this give me the first solution it
finds? What if there are multiple solutions?

It gives the first solution it finds.

It can be modified to give the Nth solution it finds fairly easily.

Try this code. You can probably modify it to make it more robust, but
it should do the trick.

Scott

--------

Option Explicit

Const N = 22
Const SolveVal = 252

Dim SolNumber As Long
Dim SolTarget As Long
Dim X(N - 1) As Long
Dim Answer(N - 1) As Long

Function FindAnswer(Val As Long, Sum As Long) As Boolean
If (Sum = SolveVal) Then
SolNumber = SolNumber + 1
If (SolNumber = SolTarget) Then
FindAnswer = True
Else
FindAnswer = False
End If
Exit Function
ElseIf (Val = N Or Sum > SolveVal) Then
FindAnswer = False
Exit Function
End If

Answer(Val) = 1
If (FindAnswer(Val + 1, Sum + X(Val)) = True) Then
FindAnswer = True
Exit Function
End If

Answer(Val) = 0
If (FindAnswer(Val + 1, Sum) = True) Then
FindAnswer = True
Exit Function
End If

FindAnswer = False
End Function

Sub SubsetSum()
Dim W As Worksheet
Dim i As Long

Set W = Worksheets("Sheet1")

For i = 1 To N
X(i - 1) = W.Cells(i, 1)
Next i

SolNumber = 0
SolTarget = InputBox("Enter solution number:")

If (FindAnswer(0, 0) = True) Then
For i = 1 To N
W.Cells(i, 2) = Answer(i - 1)
Next i
Else
If (SolTarget > SolNumber) Then
MsgBox ("Only " & SolNumber & " solutions.")
Else
MsgBox ("No solution.")
End If
End If
End Sub