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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|