A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel Query



 
 
Thread Tools Display Modes
  #1  
Old June 15th, 2004, 04:30 AM
Lesley
external usenet poster
 
Posts: n/a
Default Excel Query

Hello,

I have a list of around 50 values of which around 40 of
them make up a specific value. Is there a way that I can
find which cells make up this value?

ie.

A1 5
A2 4
A3 3

Value = 7

Therefore value is made up of A2 + A3.

Thanks in advance

Lesley
  #2  
Old June 15th, 2004, 02:25 PM
jeff
external usenet poster
 
Posts: n/a
Default Excel Query

I can' find the link, so will have to post the code.
You may have to play with this to match your setup (Dim
statement), but in order for it to run do the following:

In B1, enter your target amount
In B2, enter the # of numbers to use
In B3 thru B#, enter your numbers from which to select
the combination.
Leave Col C open for a helper col.
paste this in, say, E4; it will be the answer (or closest
value to it)
=SUMPRODUCT(($B$3:$B$203)*($C$3:$C$203=1))

run find_sol procedure.

Paste the following into a Module.

Option Explicit

Global target As Double
Global nbr_elem As Integer
Global stat(30) As Integer
Global statb(30) As Integer
Global elems(30) As Double
Global best As Double

Sub store_sol()
Dim i As Integer
For i = 1 To nbr_elem
Cells(i + 2, 3) = statb(i)
Next i
End Sub

Sub copy_stat()
Dim i As Integer
For i = 1 To nbr_elem
statb(i) = stat(i)
Next i
End Sub

Sub eval(ByVal total As Double, ByVal pos As Integer)
If pos = nbr_elem Then
stat(pos) = 0
eval total, pos + 1
stat(pos) = 1
eval total + elems(pos), pos + 1
Else
If (Abs(total - target) Abs(target - best)) Then
best = total
copy_stat
End If
End If
End Sub

Sub find_sol()
Dim StTime As Date, EndTime As Date
Dim i As Integer
StTime = Now
best = 0
target = Cells(1, 2)
nbr_elem = Cells(2, 2)
For i = 1 To nbr_elem
elems(i) = Cells(i + 2, 2)
Next i
eval 0, 1
store_sol
EndTime = Now
Debug.Print (EndTime - StTime) * 86400
End Sub

good luck
jeff
-----Original Message-----
Hello,

I have a list of around 50 values of which around 40 of
them make up a specific value. Is there a way that I can
find which cells make up this value?

ie.

A1 5
A2 4
A3 3

Value = 7

Therefore value is made up of A2 + A3.

Thanks in advance

Lesley
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.