# August Challenge of the Month Discussion



## MrExcel

Post your solutions to the August *Challenge of the Month* here.

_________________
MrExcel.com Consulting
This message was edited by  MrExcel on 2002-07-31 12:21


----------



## Jay Petrulis

Hi,

Not really interested in the CD, but *very* interested in the challenge.  This is an awesome question!!!  

First stab at it...still need to make this much more efficient.

This calls a recursive subroutine to get the cumulative combinations of N things taken 1...K at a time, then loops through the array of data points to determine if the points match the total.

Since there are 53 (yes, 53) data points to evaluate, I capped the search at up to 8 individual entries.  The limit would be 1 billion+ potential outcomes.  And since this is a first stab, I didn't want to test it with a full run.

This is a UDF which writes the answers to a column.  If called from Row 1, you can have up to 65,536 unique solutions.  The array will likely bomb with any more (else will cut off the rest???).

The function is called "Invoice Possibilities" and makes the following function calls

QuickSortVariants -- as posted by Ivan Maola.

Split97 -- as posted to microsoft.public.excel.programming by Myrna Larson (she is a retired MD and a MS MVP who is awesome).  Can be replaced with Split for those using XL 2000 or XP

Combinations -- with a call to a subroutine RecursiveCombinations.  I scoured the Internet to try to find recursive routines for VBA.  Many for C and other languages, but I cannot read them.  I have a good idea exactly how this works, but not enough of a grasp to explain it yet (soon).

I do *not* make good use of the sorted set yet.  I included it to (eventually) break out of a loop if the subtotal of the combination is greater than the target.

Also, suppose the target is negative, and all entries are positive.  That, and similar possibilities should be tested first and if true, then exit the routine with your answer.

OK, more on this as it gets refined and as others post.  Crticism/suggestions appreciated.  If there is a part that can be junked, let me know.  Always willing to redo for a good cause.

Specific questions
1.  Can collections be manipulated like arrays?
2.  Can a collection be transferred wholesale to an array?
3.  Can you delete a collection in one shot?

OK, here is the code...



		Rich (BB code):
__


Option Explicit
Dim fn As WorksheetFunction   'standard shortcut to call Excel functions in VBA

Function Invoice_Possibilities(GoalValue As Double, DataSet)
Set fn = Application.WorksheetFunction
'''  This assumes that GoalValue is > 0.

''''''''''''VARIABLE DECLARATIONS'''''''''''''''''''
'
''' First pass through the function arguments.  Text entries are eliminated.
Dim FirstSet As Variant  '''    Gets the search values (DataSet)
'                        '''    into a one dimensional array

''' If DataSet is a range of values then...
Dim Cell As Range
Dim FirstCounter  As Integer
Dim NumNegative As Integer

''' Second pass through the data is used to find out
''' the number of valid entries.  If there are no entries< 0
''' all entries greater than the GoalValue are removed.
''' Note:  Zero entries are eliminated if target<> 0!!!!  (is not introduced yet)
Dim SecondSet
Dim SecondCounter As Integer
Dim x As Integer  ' counter variable

Dim SolutionArray
Dim Counter As Double  '''must change to double from long

Dim t As Double, u As Double, v As Double, w As Double
Dim SubTotSum As Double, AnswerCnt As Long, FinalArray
Dim s As Integer, strS As String

''''''''''''DATA PREPARATION -- 1st pass'''''''''''''''''''
''' Determine the type of data in the dataset
''' and read into array FirstSet
''' Array --> "Variant()"
''' Range --> "Range"

If TypeName(DataSet) = "Range" Then
    ReDim FirstSet(1 To DataSet.Cells.Count) As Double
    For Each Cell In DataSet
        If IsNumeric(Cell) And Not IsEmpty(Cell) Then
            FirstCounter = FirstCounter + 1
            FirstSet(FirstCounter) = Cell
            If Cell< 0 Then
                NumNegative = NumNegative + 1
            End If
        End If
    Next Cell
ElseIf TypeName(DataSet) = "Variant()" Then
    FirstSet = DataSet
Else
    ' Exit function if values are unworkable
    Invoice_Possibilities = CVErr(xlErrNum)
    Exit Function
End If

'''  Exit function if no valid entries on first pass
If FirstCounter = 0 Then
    Invoice_Possibilities = CVErr(xlErrNum)
    Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''


'''''''''''''SECOND PASS''''''''''''''''''''''''''''
'
'
'''  This should allow us to start with the minimum number of
'''  choices to loop through.  If there are no negative numbers,
'''  this will eliminate any impossible values, i.e., > GoalValue.
'''  Note:  It is not possible to eliminate duplicated numbers.
'''  Suppose the out-of-balance is 375.00 and there are 3 entries
'''  of 125.00.  Dropping two duplicates will drop a solution.

If NumNegative Then
    SecondSet = FirstSet ' can't eliminate if negatives exist
Else
    ReDim SecondSet(1 To UBound(FirstSet) - LBound(FirstSet) + 1)
    For x = LBound(FirstSet) To UBound(FirstSet)
        If FirstSet(x)<= GoalValue Then
            SecondCounter = SecondCounter + 1
            SecondSet(SecondCounter) = FirstSet(x)
        End If
    Next x
    On Error Resume Next
        ReDim Preserve SecondSet(1 To SecondCounter)
    On Error GoTo 0
    Err.Clear
End If
'''  Exit function if no valid entries after second pass
If SecondCounter = 0 Then
    Invoice_Possibilities = CVErr(xlErrNum)
    Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'
''''''''''Next step -- find total possibilities'''''
''' Need to find the cumulative combinations of N things
''' taking R at a time as R goes from 1 to N.  This
''' binomial tree is equal to 2^N.  If the "select zero"
''' option is not available, there are 2^N - 1 total possible.
'''
''' Each is loaded into its own array

Call QuickSortVariants(SecondSet, LBound(SecondSet), UBound(SecondSet))

t = fn.Min(8, SecondCounter)
ReDim SolutionArray(1 To t)
For x = 1 To t
    SolutionArray(x) = Combinations(SecondCounter, x)
Next x

    For u = 1 To t
        For v = 1 To fn.Combin(t, u)
            For w = 0 To fn.Min(fn.Combin(t, u) - 1, u - 1)
                SubTotSum = SubTotSum + SecondSet(SolutionArray(u)(v)(w))
                If SubTotSum > GoalValue Then GoTo NextVlist
            Next w
            
            If fn.Round(SubTotSum, 4) = fn.Round(GoalValue, 4) Then
                AnswerCnt = AnswerCnt + 1
                If AnswerCnt = 1 Then
                    ReDim FinalArray(1 To AnswerCnt)
                Else
                    ReDim Preserve FinalArray(1 To AnswerCnt)
                End If
                For s = 0 To fn.Max(0, w - 1)
                    strS = strS & SecondSet(SolutionArray(u)(v)(s)) & "| "
                Next s
                FinalArray(AnswerCnt) = Left(Trim(strS), Len(Trim(strS)) - 1)
                strS = ""
            End If
        
NextVlist:
        SubTotSum = 0
        Next v
    Next u

If AnswerCnt = 0 Then
    Invoice_Possibilities = "no matches found"
Else
    Invoice_Possibilities = fn.Transpose(FinalArray)
End If
End Function



Function Combinations(ByVal N As Integer, ByVal K As Integer)
Dim CombinCollection As New Collection
Dim x As Long
Set fn = Application.WorksheetFunction

ReDim CombinArray(1 To fn.Combin(N, K))

RecursiveCombinations CombinCollection, N, K, 1, ""

With CombinCollection
    If .Count = 0 Then
        Exit Function
    Else
        For x = .Count To 1 Step -1
            CombinArray(x) = Split97(Left(CombinCollection(x), _
            Len(CombinCollection(x)) - 1), ",")
            
            CombinCollection.Remove (x)
        Next x
    End If
End With
Combinations = CombinArray
End Function



Sub RecursiveCombinations(CombinCollection, ByVal N As Integer, _
ByVal K As Integer, ByVal i As Integer, ByVal strArray As String)
Dim Counter As Double

If K > N - i + 1 Then Exit Sub
If K = 0 Then
    CombinCollection.Add strArray
    Exit Sub
End If

RecursiveCombinations CombinCollection, N, K - 1, i + 1, strArray & i & ","
RecursiveCombinations CombinCollection, N, K, i + 1, strArray

End Sub




Sub QuickSortVariants(vArray As Variant, inLow As Long, inHi As Long)
      
'''  Routine posted by Ivan F. Maola to MrExcel.com Message Board
'''  http://www.mrexcel.com/board/viewtopic.php?topic=16211&forum=2
'''  Original author unknown
'''  Comments deleted in code below


   Dim pivot   As Variant
   Dim tmpSwap As Variant
   
   Dim tmpLow  As Long
   Dim tmpHi   As Long
    
   tmpLow = inLow
   tmpHi = inHi
    
   pivot = vArray((inLow + inHi)  2)
  
   While (tmpLow<= tmpHi)
  
      While (vArray(tmpLow)< pivot And tmpLow< inHi)
         tmpLow = tmpLow + 1
      Wend
      
      While (pivot< vArray(tmpHi) And tmpHi > inLow)
         tmpHi = tmpHi - 1
      Wend

      If (tmpLow<= tmpHi) Then
      
         tmpSwap = vArray(tmpLow)
         vArray(tmpLow) = vArray(tmpHi)
         vArray(tmpHi) = tmpSwap
         
         tmpLow = tmpLow + 1
         tmpHi = tmpHi - 1
      End If
   
   Wend
  
   If (inLow< tmpHi) Then QuickSortVariants vArray, inLow, tmpHi
   If (tmpLow< inHi) Then QuickSortVariants vArray, tmpLow, inHi
  
End Sub



Function Split97(sString As String, Optional sDelim As String = " ", _
Optional ByVal Limit As Long = -1, _
Optional Compare As Long = vbBinaryCompare) As Variant

''''''''''''''''''''''''''''
'   Split97 mirrors the Split function introduced in XL2000
'   Author Myrna Larson
'   posted to microsoft.public.excel.programming 13 Nov 2001

Dim vOut As Variant, StrLen As Long
Dim DelimLen As Long, Lim As Long
Dim N As Long, p1 As Long, p2 As Long

StrLen = Len(sString)
DelimLen = Len(sDelim)
ReDim vOut(0 To 0)

If StrLen = 0 Or Limit = 0 Then
 ' return array with 1 element which is empty
ElseIf DelimLen = 0 Then
    vOut(0) = sString ' return whole string in first array element
Else
    Limit = Limit - 1 ' adjust from count to offset
    N = -1
    p1 = 1
    
    Do While p1<= StrLen
        p2 = InStr(p1, sString, sDelim, Compare)
        If p2 = 0 Then p2 = StrLen + 1
        N = N + 1
        If N > 0 Then ReDim Preserve vOut(0 To N)
        If N = Limit Then
            vOut(N) = Mid$(sString, p1) ' last element contains entire tail
            Exit Do
        Else
            vOut(N) = Mid$(sString, p1, p2 - p1) ' extract this piece of string
        End If
            p1 = p2 + DelimLen ' advance start past delimiter
    Loop
End If
Split97 = vOut
End Function


I haven't finished commenting this, and you'll notice that my variable declarations got a lot less descriptive as I went on -- blame it on laziness.

_________________
Bye,
Jay

EDIT:  Changed the separator to a | from a comma to eliminate confusion with systems having the comma as the decimal separator.
This message was edited by  Jay Petrulis on 2002-08-02 08:01


----------



## Legacy 5624

> On 2002-07-31 12:08, MrExcel wrote:
> Post your solutions to the August *Challenge of the Month* here.
> 
> _________________
> MrExcel.com Consulting
> This message was edited by  MrExcel on 2002-07-31 12:21




Could you post the link to the recent posting on the MS boards that you mentioned, please?


----------



## Legacy 5624

“The August challenge is to document a method for determining which invoices the customer is paying. If there is more than one combination, note that. We are looking for the best general purpose algorithm that could be used every day by accounts receivable departments across the world for similar problems.”


This is an interesting mental exercise but in the real world, I cannot imagine that it is a necessity or even desirable.
It involves guessing the payer’s intention. If a match is found that is not made up of the earliest dated invoices, it may not be appropriate to assume that the payer intended to settle these invoices – he may want the payment offset against the earliest invoices.
In any event, because of the potentially huge number of combinations, I doubt that there is an Excel solution that will prove to be practical.

Normal practical requirements merely need to set off unallocated payments against the earliest dated invoices (with a partial offset for the last one if necessary) and then to advise the payer to what invoices the payment has been applied.
It is simple to automate this - both the set-off and the issue of the advice to the payer.
Even if an exact set off is made, whether or not it is against the earliest invoices, an advice to the payer is still advisable procedure.

Since earlier invoices are normally settled first, perhaps a partial automated check could be run to try matching the payment with the invoices.
For example, the combinations could be checked for the earliest invoices that add up to just over the amount paid plus (say) 4-5 invoices. If a match were found, OK – otherwise just set off against the earliest invoices.
This would remove the potentially impractical processing of huge combinations.

It’s an interesting challenge, but I don’t think it’s one that has much useful application in the context of managing accounts receivable.


----------



## Jay Petrulis

Hi,

The link is here...

http://groups.google.com/groups?hl=...01c22d34%247aa68d40%24a4e62ecf%40tkmsftngxa06 

It is also in my posting to Chris Davison's thread "Longest Macro" in the XL-Lounge from a few days ago.

This problem won't be "solved" with an algorithm, at least not by anyone using Excel by August 31.  This challenge is a "Holy Grail" of computer science.  However, that doesn't mean the exercise is of no use.  In fact, I think it is highly instructive about using Excel and has a lot of practical use for smaller data sets.  So I wouldn't worry too much about how truly applicable this is for the specific problem description.

Bear in mind Harlan's message in the newsgroup thread that human capacity to find the match(es) far exceeds the capacity of the fastest supercomputer.  

The key part is to try to remove as many combinations as you can.  The more you discard the faster the program will run through the valid combinations.

In Chris' thread, I think PaddyD responded about this being a P=NP (complete or hard???) problem, like the travelling salesman and the knapsack problem.

BTW, the travelling salesman problem has been solved (...find the shortest route a salesman will take if he is to visit each of the xxxx number of US cities/towns over some threshold population, visiting each locale once).  It was done with a distributed grid computer (thousands of linked computers lending their muscle) after two weeks or so of continuous processing!!!!

Give it a shot.  A group effort will result in a routine that will be optimum given our limited resources and abilities (some of the greatest minds in mathematics and computer science have worked on this, so you and I won't get too far).  In any event, I learned a ton from the thread and trying the exercise.  Case in point -- look at my routine.  At the end I am looking at an array containing an array of arrays -- SolutionArray(u)(v)(w).  I am sure that that can be improved, but I didn't know how to use arrays in anyway like this manner until I gave it a shot.

We have a full month to work on this, so the best effort will show some nice progress.

_________________
Bye,
Jay
This message was edited by  Jay Petrulis on 2002-08-02 21:08


----------



## Chris Davison

how many answers are there ?

I got one set in about 3 mins running time if it's any help...

680.23
98.40
444.98
324.84
978.53
911.45
409.17
718.32

just a very basic brute-force but random method.... I'll keep tinkering
This message was edited by  Chris Davison on 2002-08-03 01:04


----------



## Chris Davison

OOoo, and another

895.39
83.06
507.08
230.72
911.45
329.17
673.47
228.31
698.27


----------



## Chris Davison

192.65
194.58
444.98
630.92
978.53
144.77
329.17
116.14
718.32
441.43
365.43


----------



## Chris Davison

764.18
673.47
228.31
185.58
925.39
722.73
691.83
365.43


----------



## Jack in the UK

Hi Guys as you know i work in Finanace 15 yers and Debt Recovery / County Courts and all the rest in my times.

I feel this challenge is a poser, but underline TOTALLY pointless in the cut and thrust on debt, all invoices are debt untill PAID.

You can never work out what invoices someone will pay in advance its not like that as one bill can be in dispute or not POD fully 1000000 reasons all different or the old i ant goona pay that one till next week, thats where credit contrl comes in of cause.

In the UK this challenge is i feel against English law, that is as the idea is to work out and prepair people to pay with an system to work out what they wil pay,

ALL camoants in sales offer terms of payment credit if you like if 1 day COD or 30/ 60 days whatever, so this is the cut line, but remember guys pay in advance so that will make any system fail again

Ledgers that are cleaverly constructed are able to highlight debt and expected payments no VBA will run to give the correct results as the payer will throw a spanner in the works evertime and you time and effort will never help, on spent time re working the results.

Finance and debt is risk related so look and new debts and thats what yo need to collect matters not if or how

say i need £100

Bill should pay £60
Juan should pay £14
Jack should pay £16
Dave Should pay £30

Im £20 over but credit management hows i need only £100 the £20 in this case is cream

But say i get Chris to pay £50 i do not need Daveand Jack to pay.

So prepairing data is not workable: Debt is collected in terms and out and deals on debts also so thats the wild card

Only advice i can give is stick to law, and help you debtors and encourage them to pay, polit and friendly is best nOT hostile.

I collect £1,000,000 or $1,555,000 so my debt reduces that a top heavy collection ie im doing myself out of a job!!! i collect to much, as stastics say - so i do an ok job each month 30 day cycles.

What would do the best to answer this would be ODBC all debts in invoice format attached required details and then auto mate in letter and reports production, ie send letter or call debtor

No waht you need to do is report this debt so that you know each day what overdue and you have attacked it, now run that week and then months cross check unpaid to paid to give you collect rate % 60 to 68% is in the zone, or more that current debts occured.

Then you will have your answers.

NEVER will 100% be collected unless very few debtors to collect from i have thousands each month, i know most of them thou:

Also in letters you can trans ISO detail [invoice details] to be fired into letters...


----------



## Jack in the UK

Re readin the question set by Bill im more preplexed, i can now even see the issue receivable / sales ledger / credit control, will or should eat this alive, 52or54 is not that many if on one user account, the account package will be able to run tally this and so will be easy enough. The debtor should be able to send a remitance advice or call them direct to say, oh explain this one.

If sales ledger in Excel than is not an accounting pacage as discribed. And thats not a real questin as they conflict.

Saying that 54 invoices on one account in 30 day or say 120 max is fine but after that would be legasy debts never paid in full so impossible to program.

Im not blunting the question as i know code will do as you want, but all im saying is its not workable so dont rely on it, nothing will or can substute for keen staff with sharp eyes.

Im preplex and who the 54 are obtained as if not pasteable in excel i can see the saving!!!


----------



## Jay Petrulis

Chris,

The brute force method is the only way this is going to be "solved" but you can use all sorts of techniques to narrow the scope a bit.  

The systems designed to solve these problems use genetic algorithms where the program tries to learn from its "mistakes" and focus on only the possible successful outcomes.  They also use simulated annealing (kicking the program out of a node that is going nowhere), and branch and bound techniques.  The details are way beyond me.

Your code from the "longest macro" thread can be used to do this.  One thing to speed it a bit -- since we are dealing with combinations instead of permutations, you can use the following

<pre>For i = 1 To n - 2
    For j = i + 1 To n - 1
        For k = j + 1 To n
            'do stuff
        Next k
    Next j
Next i</pre>

rather than

<pre>For i = 1 To n 
    For j = 1 To n 
        For k = 1 To n
            ' eliminate duplicates and do stuff
        Next k
    Next j
Next i</pre>

as you go further up the levels, significant savings will start to show.

Jack,

Don't get hung up on the specifics.  Consider another situation.  I work at a trust bank where there is a need to reconcile pension fund statements.  When the accounts do not balance, it is up to the analyst to find the discrepancy.  Usually, it is one entry that is causing the problem, as it is not mapping through all the investment schedules correctly.  In no way will the analyst be able to know this ahead of time (nor will the client or money manager have any details about this).  There are no legal rules that must be followed.  The out-of-balance must be found and corrected.  Any systematic procedure would help.  Of course, there are other guides used to spot the problems, but sometimes you just have to pour through the data and find the number(s).


----------



## Mark W.

> On 2002-08-03 01:01, Chris Davison wrote:
> how many answers are there ?
> 
> I got one set in about 3 mins running time if it's any help...
> 
> 680.23
> 98.40
> 444.98
> 324.84
> 978.53
> 911.45
> 409.17
> 718.32
> 
> just a very basic brute-force but random method.... I'll keep tinkering
> This message was edited by  Chris Davison on 2002-08-03 01:04



I'm guessing that 98.4 is a typo meant to  be 89.4.


----------



## Mark W.

Here's another solution set...

77.74
116.14
192.65
194.58
222.52
538.64
680.23
691.83
1842.59


----------



## Mark W.

...And another...

77.74
89.40
116.14
185.58
192.65
194.58
228.31
230.72
324.84
329.97
346.35
365.43
456.68
507.08
911.45


----------



## Ian Mac

I sat and did about 5 or 6 brute force solutions at work, I didn't post them as I didn't think THAT was that problem set. Forgive me but we're not going to get anywhere quoting what's right in the numbers set (next week it may be 67 and whole different set of numbers).

Did nobody read Jays:

_This problem won't be "solved" with an algorithm, at least not by anyone using Excel by August 31. This challenge is a "Holy Grail" of computer science. However, that doesn't mean the exercise is of no use. In fact, I think it is highly instructive about using Excel and has a lot of practical use for smaller data sets. So I wouldn't worry too much about how truly applicable this is for the specific problem description. 
_

I came into this Excel thingy from a Graphic design background, but fully understand the need to try and stretch to the challenge.

BUT, Jay, why arn't you interested in the CD?? I worked on 100 of the files and there's some great stuff on it.

That's all I have to say, rant rant rant 

Regards,

Don't argue why the wealth is there, why we need the wealth anyway, or who has it, just SHARE IT!!.


----------



## Mark W.

Here are the 1st 30 rows of the Solver model that is producing my results.  The model parameters are saved (and reloadable) from cells F1:F5.Book2.xlsABCDEFG177.7410.000.00283.06154.00389.401FALSE4116.140TRUE5126.690100.006144.7717160.6218185.5809192.65110194.58011219.10012222.52013228.31114230.72015244.22116280.71017324.84018329.17119329.97020346.35021365.43022409.17023440.93124441.43025444.98026456.68027507.08028515.11029538.64030542.120Sheet1

As of this point in time this model has produced the following solution sets...

77.74,	116.14,	192.65,	194.58,	222.52,	538.64,	680.23,	691.83,	1842.59

77.74,	89.40,	116.14,	185.58,	192.65,	194.58,	228.31,	230.72,	324.84,	329.97,	346.35,	365.43,	456.68,	507.08,	911.45

77.74,	83.06,	89.40,	144.77,	160.62,	192.65,	228.31,	244.22,	329.17,	440.93,	978.53,	1587.52
This message was edited by  Mark W. on 2002-08-03 17:49


----------



## Chris Davison

in terms of processor speed, is it any quicker to check if 1002, 2003, 3004 etc add up to 5,007 (ie whole numbers) than it is to check whether 10.02, 20.03, 30.04 etc add up to 50.07 (ie decimals) ?


----------



## PaddyD

Following some prompting from Jay, and as it's only slightly tangential to the main challenge:



> This problem won't be "solved" with an algorithm, at least not by anyone using Excel by August 31.  This challenge is a "Holy Grail" of computer science...



To the extent that this problem relates to the P=NP? question, it might be worth pointing out that a full solution could earn you both the Mr Excel CD and $1 million.  I am agnostic about which prize would be more valuable.



> In Chris' thread, I think PaddyD responded about this being a P=NP (complete or hard???) problem, like the travelling salesman and the knapsack problem...
> 
> BTW, the travelling salesman problem has been solved...



That's not strictly true.  The solution of a particular version of the travelling salesman (TS), while interesting to salesman & those concerned with the general issue of algorithmic optimisation, doesn't really bare on the general reason why TS is famous - i.e. does P=NP?

As this is a _Microsoft_ Excel forum, is seems appropriate to point to a link that (a) explains the P=NP? issue in some detail, and (b) relates it to Minesweeper:

http://www.claymath.org/prizeproblems/milliondollarminesweeper.htm

Paddy
This message was edited by  PaddyD on 2002-08-04 19:18


----------



## Sharad Kothari

The outline of code is as follows:

1. First,  all the values is stored in an Array in Ascending Order. This is been done by putting all the values in a Range in Excel, Sorting it and loading it in Array.
2. A recursive routine is used to find out the Target Total. 
3. This recursive routine works in the reverse order from the last (and highest point of array).
4. The last point is extracted and checked against the Target Sum. If this last point is higher than the Target Sum, then routine moves to the next highest point.
5. If it is less than Target sum, then this point is Stacked. A recursive call is made, whereby the target is reduced by this point and the list is curtailed, immediately preceding this point.
6. If the at any point the target sum can be found out, the entire stack can be extracted, which in the given code is extracted on the spreadsheet. The code keeps on working on the remainder value.
7. If in a particular recursive call, the target is not found, the last value from the Stack is removed.


' Code

Option Base 1
Private Stack() As Double
Private CurRow As Integer

Sub temp()
' Subroutine to do the preliminary work

Dim aList() As Double
Dim iListCount As Integer
Dim dTarget As Double
CurRow = 5
ReDim Stack(1)

' "rngList" is sorted in Ascending order
iListCount = Application.Range("rngList").Rows.Count
ReDim aList(iListCount)

dTarget = Range("rngTarget")

For n = 1 To UBound(aList, 1)
    aList = Range("rngList").Cells(n, 1)
Next

SearchTarget dTarget, aList

End Sub

' Main Recursive Routine
Sub SearchTarget(dTarget As Double, aTargetList() As Double)
Dim aRevisedList() As Double, dRevisedTarget As Double

For n = UBound(aTargetList, 1) To 1 Step -1

    Select Case aTargetList
        Case Is = dTarget
            For m = 1 To UBound(Stack, 1)
                Cells(CurRow, m + 4) = Stack(m)
            Next
            'MsgBox ("Found!")
            Cells(CurRow, m + 4) = dTarget 'the last value which is not Stacked
            CurRow = CurRow + 1

        Case Is< dTarget And n > 1
            aRevisedList() = aTargetList
            ReDim Preserve aRevisedList(n - 1)
            dRevisedTarget = dTarget - aTargetList
            ReDim Preserve Stack(UBound(Stack, 1) + 1)
            Stack(UBound(Stack, 1)) = aTargetList
            SearchTarget dRevisedTarget, aRevisedList
            ReDim Preserve Stack(UBound(Stack, 1) - 1)
    End Select
Next


End Sub

EDIT: I have posted revised and updated code on 25th August. - Sharad Kothari
This message was edited by  Sharad Kothari on 2002-08-25 02:59


----------



## eejit

" 3. This recursive routine works in the reverse order from the last (and highest point of array). "

But it could equally be done by working from the lowest to the highest. So how could it be established (maybe not possible) whether it is quicker to work from the top or from the bottom?
(And what about the run time if there is no match!)

The thing that's being overlooked is that the original question wants a routine to match payments against invoices.
W. Pooh has indicated that this is not a practical or worthwhile target - I agree with him.

Sorting by invoice value can(should) substantially reduce the number of combinations to be checked but it will not necessarily produce results that will represent matches in ascending order by invoice date - which surely must be important.
If it's not important, what's the relevance to accounts receivable and why bother trying to match at all?
If it is important, whatever the match happens to be, the payer needs to be asked if the correct invoices are being matched. So why bother to attempt a match - just either ask the payer or set off against the earliest invoces.

Jay Petrulius has said that the challenge is appropriate for identifying errors in a particular situation that he describes.
OK, but this is not what the challenge has asked for.
Different considerations are involved in developing a procedure for J. Petrulius' scenario versus the scenario set by the monthly challenge.

Perhaps the challenge should drop the association with invoices/accoumts receivable, and merely ask for a solution to match a given amount with a range of numbers in any sequence.
That being the case, good luck!
I shall certainly not be wasting any of my time on it.


----------



## Sharad Kothari

"So how could it be established (maybe not possible) whether it (recursive routine) is quicker to work from the top or from the bottom?"

If you build a pyramid from top to bottom or bottom to top, over all time would not vary. However, while working top to bottom, the halfway mark would be reached early and that is why recursive routine is build to work from bottom to top. With a few modifications, it can be turned to work from top to bottom. It's just a matter of preference.


"The thing that's being overlooked is that the original question wants a routine to match payments against invoices."

This is not being overlooked. All the possible combinations is immediately extracted as soon as they are found. 

"but it will not necessarily produce results that will represent matches in ascending order by invoice date - which surely must be important."

The dates are not contemplated in the given Challenge. But dates, if they can be used, can certainly be used to filter the results and reduce the processng time.

"So why bother to attempt a match - just either ask the payer or set off against the earliest invoces."

Any person, who has ever tried to reconcile any figures, would certainly appriciate a workable solution to the given challenge.


----------



## eejit

"If you build a pyramid from top to bottom or bottom to top, over all time would not vary. However, while working top to bottom, the halfway mark would be reached early and that is why recursive routine is build to work from bottom to top. With a few modifications, it can be turned to work from top to bottom. It's just a matter of preference."

I'm not sure that your logic is accurate.
Working from bottom to top with the data given in the challenge can eliminate very quickly many of the total possible combinations - I think more quickly than working from top to bottom.

"This is not being overlooked. All the possible combinations is immediately extracted as soon as they are found."

But they are not being extracted in a date sequence - which is relevant for matching invoices with payments. 

"The dates are not contemplated in the given Challenge. But dates, if they can be used, can certainly be used to filter the results and reduce the processng time."

This was exactly my point about the relevance to accounts receivable - the Challenge is about accounts receivable so there is an inherent importance re dates.

"Any person, who has ever tried to reconcile any figures, would certainly appriciate a workable solution to the given challenge."

You are missing the point. I hereby state categorically that the solution requested by the Challenge is absolutely useless for the purpose of maintaining accounts receivable records.
There may be other scenarios that might benefit from a solution but then the Challenge refers only to matching payments/invoices. It is highly probable that the structure of a solution for other scenarios would involve different considerations than the scenario per the Challenge.


----------



## s-o-s

Why is it that everybody is looking for a reason to work on this challenge...Just do if you want to try or move on if not....

In addition to Jay's alternate suggestion where this would help...

I have helped out someone in the past who had a problem that this would fix...My solution was using the brute force method I'm afraid and I warned that it was best to limit the number of selections to make up the total starting at 1 selection then rising up to 10 selections. I listed all solutions that came up to the total.

The solution was for the problem...

I work in an accounts office and have just taken over from someone else...he used to amalgamate loads of balances from a detailed trial balance into a summary presented to clients for sign off, however he only ever entered values...How do I find out which balances are included in any given summary figure? 

So now a couple of very valid reasons exist and 1 which is arguable(at least so far!). Lets see some proper solutions to this challenge and less rubbish about why not to try. 

Personally I'm interested in the challenge to see if better methods come up, I'll even see if I can revamp some of my old code.

_________________<MARQUEE/><A HREF= "http://website.lineone.net/~s-o-s/Index.html">
	

	
	
		
		

		
			





</MARQUEE>

Edit to correct spellings and delete a duplicate Post...
This message was edited by  s-o-s on 2002-08-06 08:28


----------



## eejit

S-O-S said :-
"I have helped out someone in the past who had a problem that this would fix...My solution was using the brute force method I'm afraid and I warned that it was best to limit the number of selections to make up the total starting at 1 selection then rising up to 10 selections. I listed all solutions that came up to the total."

I think this is a very practical approach - W. Pooh suggested the same thing.

However, your other comments about just finding a solution instead of "rubish" about why not to try are rejected.

Any "solution" depends upon what is trying to be achieved.
Payment/invoice matching involves different requirements versus your example of matching summarised figures with a list of balances.

So what is it that is supposed to be achieved?
Matching payments/invoices?
If so, it's a useless exercise - no use to anyone.

If it's something else that is to be achieved, then what is it?
Any solution is dependant upon what is required.


----------



## Jay Petrulis

> ---snip---
> However, your other comments about just finding a solution instead of "rubbish" about why not to try are rejected.
> 
> Any "solution" depends upon what is trying to be achieved.
> Payment/invoice matching involves different requirements versus your example of matching summarised figures with a list of balances.
> 
> So what is it that is supposed to be achieved?
> Matching payments/invoices?
> If so, it's a useless exercise - no use to anyone.
> 
> If it's something else that is to be achieved, then what is it?
> Any solution is dependant upon what is required.



For those who want to quibble over the semantics of the challenge, how about this?

<pre>Given a list of numbers, find all possible combinations whose sum equal a certain value.</pre>

What is *required* is for you to list out the combination(s) of values from the list of 54 that equal 4,556.92.  Don't read anything else into this, despite what you have read previously.  Answer the restated question and then we can go on about its usefulness or lack thereof.

S.O.S. hit it directly -- let's see what you have rather than discuss the "meaning" of the challenge.  

This is just a friendly competition and valuable solution time is being wasted while we are arguing about the question. 

The routine I originally posted is extremely slow.  I have changed it a bit, but it is still a (major) work in progress.  

Fastest would likely be Mark W.'s (and Tushar Mehta's suggestion from the original newsgroup thread), yet those *may* not exhaust all possibilities.

Sharad Kothari's post looks very promising (I haven't tested it yet), so in any event there are some nice options out there already.


----------



## MrBluesman

Looking at this challenge it has a similar problem to a previous challenge (The steel Bar one) and best use of material to cut down on waste. So finding a solution to this does have other benefits. 

I have to agree with Jay and SOS do it if you want the challenge if you don't you can always phone up the company and ask them what invoices they are paying!


----------



## Brabantio

> On 2002-08-08 04:48, MrBluesman wrote:
> Looking at this challenge it has a similar problem to a previous challenge (The steel Bar one) and best use of material to cut down on waste. So finding a solution to this does have other benefits.
> 
> I have to agree with Jay and SOS do it if you want the challenge if you don't you can always phone up the company and ask them what invoices they are paying!




"I have to agree with Jay and SOS do it if you want the challenge if you don't you can always phone up the company and ask them what invoices they are paying!"

Exactly!
So as far as accounts receivable are concerned, you will have to check with the paying company anyway - whether you find one match, more than one match, or no match.
In other words, it's not applicable to accounts receivable and is therefore not the same as the "Steel Bar one", is it?


----------



## manet

I’ve just read through the whole of this thread and think there is a need to sort out all the ********.

The challenge asks for a solution for matching payments against invoices so that it “could be used every day by accounts receivable departments across the world for similar problems”.

I agree with the posters who say that the challenge is inapplicable to managing accounts receivable.

There are obviously other situations where it may be applicable, but any attempt at a solution has to take account of the actual situation and the aim of the “solution”.

Jay Petrulius has taken it upon himself to re-define the challenge and to re-set the parameters.

I would suggest that the challenge should be the one defined by Jay Petrulius so that no more ******** needs to be posted.


----------



## IOANNIS

If anybody want to try the challenge just open the Excel and wright a macro ..if not just try the www.i_cant.com or www.i_dont.com ...

Anyway ...

I made a macro and after 6 hours i find more than 70000 solutions (more than 10000/hour) 

and there are mush more .. until it ends ...

i made olso and a Resume one to continue if stoped it  

How Many are the solutions (check=4556,92) ?

Does Anybody knows ?


----------



## Chris Davison

> How Many are the solutions?
> 
> Does Anybody knows ?



that's the challenge. How many are there, and list them.


----------



## Jeffrey Chin

Hi all,

this is my attempt to the "Holy Grail" challenge.. being a practical person, i prefer a quick fix to the problem to an academic approach.. my attempt is not a "find all solutions" method (i'll leave that to the academics) but a solution which is a quick fix to the accounts receivable department problem which took me about 2 hours.. here goes..

you may think it is crazy to randomly pick numbers fro the list to add up to 4556.92.. but this method is easy and it produces millions of guesses in a short time.. however, i hv an added twist to this (if not, you'll take forever to find just one solution).. i hv decided on a "half-guess half-calculate" method..

i will call the list of 54 (or 53) given numbers the universal set and the a list of numbers that add up to 4556.92 the solution set..

first, i produce a list of added pairs of numbers from the universal set.. this is because i know for sure that a potential solution set will have at least 2 numbers (any 2).. 53 numbers will give 1378 combination of added pairs.. you can derive this manually, which is not too difficult or write a simple macro for this.. i did manually..

second, i generate a potential solution set by randomly picking numbers from the universal set, taking the difference of the numbers with 4556.92 and comparing the difference with the list of added pairs to get 4556.92.. that is to say, the sum of randomly picked numbers plus an added pair = 4556.92.

you may say this is really weird.. but who cares, so long as it works and i can produce the solution for the accounts dept in  2 hours! (being practical).. well, the reason for producing the added pairs is to drastically reduce the guessing time for a solution set by greatly increasing the probability of a perfect match.. each solution set takes less than 30 seconds to run..

i hv problems attaching my spreadsheet and macro (can someone show me?).. anyway, these are some of my solutions using the above method..
 185.58 
 194.58 
 222.52 
 228.31 
 230.72 
 630.92 
 1,021.70 
 1,842.59 

 630.92 
 144.77 
 324.84 
 515.11 
 538.64 
 628.89 
 789.77 
 983.98 

 89.40 
 126.69 
 160.62 
 280.71 
 698.27 
 718.32 
 895.39 
 1,587.52 

 409.17 
 722.73 
 858.97 
 978.53 
 1,587.52 

 77.74 
 192.65 
 222.52 
 346.35 
 507.08 
 722.73 
 2,487.85 


 440.93 
 444.98 
 456.68 
 673.47 
 698.27 
 1,842.59


----------



## IOANNIS

..that's the challenge. How many are there, and list them...

.. After a few modifications on my macro  i realise that the total solution are too many ..

until now 295242 solutions

6000 to 17000 sol/hour

PAIR STATISTICS

TOTAL	295.242
01.02	144.682
01.03	78.894
01.04	42.585
01.05	23.701
01.06	5.380

.. 01 is the first number
.. 02 is the second 
   and so on
.. the numbers are sorted ascending


(I think that my english is not too bad ...)


----------



## IOANNIS

to Jeffrey Chin
You find solutions (randomly) because the total solutions of spesific sum are millions 

what about 1546.66 which has only 19 in the same compinations with 4556.92

..i found all (19) in less than 30 sec ..


----------



## Smiffy

To Jeffrey Chin

And how long will it take if there are no matches ?


----------



## Jeffrey Chin

Hi IOANNIS

i donno if im lucky or wat.. as i said my method is not a "find all solutions" method but im able to find at least 3 solutions in to 1546.66 in giffy..
 116.14 
 244.22 
 324.84 
 346.35 
 515.11 

 77.74 
 192.65 
 222.52 
 515.11 
 538.64 

 77.74 
 116.14 
 160.62 
 280.71 
 911.45 

btw, i donno if there are 19 solutions for 1556.66 becos i threw away some like
 83.06 
 346.35 
 440.93 
 329.97 
 346.35 
where 346.35 is repeated.. cld u verify the 19 solutions pls.. same for 4556.92, i find it hard to believe there are hundred and thousands of solutions..

Hi Smiffy,

my macro runs forever if there is no match.. i guess i can put a stop to say 100,000 guesses if i want.. like i said, for this quick fix method for the accounts dept, i think they wld be happy to take say 10 solutions to start making phone calls! 

well guys,
i'll try if i hv a brute force method to find all solutions..


----------



## IOANNIS

TOTAL SOLUTIONS OFF 4556.92 

712473 ONLY  !!!

..(If the macro has no bugs)


----------



## IOANNIS

TOTAL SOLUTIONS OFF 4556.92 

712473 ONLY  !!!

..(If the macro has no bugs)


----------



## IOANNIS

AUGUST_2002_53_02.xlsBCDE4FIRSTNoSOLUTIONS50177,74322.15160283,60175.82570389,4095.992804116,1451.315905126,6928.6591006144,7716.1421107160,629.0711208185,585.4071309192,653.1511410194,581.9171511219,101.1231612222,526641713228,314441814230,722081915244,221462016280,711002117324,84482218329,17372319329,97172420346,35172521365,43182622409,1792723440,9392824441,4312925444,9803026456,6813127507,8003228515,1103329538,6403430542,1203531589,1803632628,89137TOTAL712.473C_08_2000_STAT_54


----------



## Jeffrey Chin

Hi Ioannis

wat does the table mean?
does it mean the number 77.74 appears in 322,151 times out of all the 712,473 solutions?


----------



## IOANNIS

Hi Jeffrey Chin

it means that ..

Solutions with the first the number= 77.74 
are 322,151 

 1 2 3 4 5 6 7 8 9 10 11 12 13 15 18 27 29 45
 1 2 3 4 5 6 7 8 9 10 11 12 13 16 32 35 47
 1 2 3 4 5 6 7 8 9 10 11 12 14 15 18 19 33 48
 1 2 3 4 5 6 7 8 9 10 11 12 14 15 19 25 28 48
 1 2 3 4 5 6 7 8 9 10 11 12 14 15 22 27 32 39

Solutions with the first the number= 83,60 
are 175.825

 2 14 15 19 21 23 26 28 29 32 39
 2 14 15 19 21 25 26 35 43 44
 2 14 15 19 21 26 28 32 38 49
 2 14 15 19 24 26 28 32 40 44
 2 14 15 19 25 30 31 32 34 41
 2 14 15 19 25 37 38 45 46
 2 14 15 19 26 32 34 47 49
 2 14 15 20 21 24 29 35 40 44

and so on

the 77.74 appears only in 322,151 solutions of total

the 83,60 appears in 175.825 solutions as first number but appears olso in some of 322,151 of 77.74 as second number ..

 1 2 22 25 29 32 37 40 46
 1 2 22 31 32 43 47 49
 1 2 22 31 37 44 46 47
 1 2 23 31 35 39 48 49
 1 2 23 35 39 40 44 47
 1 2 24 29 30 34 36 38 41
 1 2 24 35 36 37 44 50
 1 2 24 35 37 40 41 50
 1 2 27 31 36 40 52
 1 2 28 35 38 41 42 45


----------



## Jeffrey Chin

Hi Ioannis

maybe u wanna check your program again cos 444.98 appears at least once in one of my solutions..


----------



## IOANNIS

Tell me the solution with 444.98 as first number (the others numbers must be greater than 444.98 )  Jeffrey Chin  ?


----------



## Jay Petrulis

Hi,

Ioannis -- I may be misreading your solution set but the following solution index numbers

1 2 3 4 5 6 7 8 9 10 11 12 13 15 18 27 29 45 
and
1 2 3 4 5 6 7 8 9 10 11 12 13 16 32 35 47 

sum to 
4555.66
and	
4556.38

Please check to make sure your program is giving exact matches.  Again, if I am misreading your result set, please tell me.

For all -- a few hints that may or may not be of help...

1.  The total "possible" combination is 2^54 - 1 (if you don't count the selection of 0 things).

2.  Since there are no negative numbers, you can eliminate any values > the target value.  Here, that allows us to reduce the number of items in the search data by one, to 53.

3.  If you sort the original set in ascending order, and then take a running total, you will notice that the first 22 items sum to > the target value.  Therefore, you can eliminate immediately any combinations of 53 things taken > 21 at a time.

4.  For an exhaustive search, the problem now is reduced from Sum(Combin(54,1...54)) to Sum(Combin(53,1...21))

There are now  761,008,457,488,847 unique potential solutions.  This number is nearly 24 times less than the original 2^54.

5.  That number, though, is still far too high to be practical.  If you can process 10,000,000,000 entries in a day, it will take 200+ years to go through the list.

6.  The key to resolving the above is to break out of sequences that are going nowhere.  If you can do that early, you can probably reduce the total searches by factors of thousands or even millions.

The above discussion deals with making brute force methods more efficient.  The Solver solutions and Sharad's routine appear to be much more efficient in the search.  Ionannis and Jeffrey Chin have other approaches which will be quite informative when posted, too.  

Good job all.


----------



## Chris Davison

Jay,

just a lateral thought :

we could reduce those 200 years down to a single year *if* we were able to incorporate into the code a method for splitting up the search into 200 different spreadhseets, sent to 200 volunteers, or a month if you split it down amongst 4,000

(not practical for accounts departments, yes, but let's forget abotu that, we're just interested in finding the solutions)

how easy would it be to slice the search up into 4,000 manageable segments - each segment being the next set to be searched when the spreadsheet was next copied and ran ?

surely it's possible to do this and then have the code email it's particular segment set of solutions back to the original person or project leader

[I'd been thinking about this along the lines of the SETI project where rather than have a single computer trying to analyse zillions of bits of info : packets of info are farmed out to volunteers across the world who each analyse their own little segment then post their results back]

I'm not saying we *need* 4,000 volunteers, what we need is the code that could do this.....

I don't know much about arrays in VBA, but know a simple way to allocate ranges of permutations based on their "binary address" (for want of a better way to describe it).... this would allow a block of 10,000,000 combinations to be checked easily

but if we could get the next version of the spreadhseet to check the next 10,000,000 combos, etc etc, that would work.....

any thoughts anyone ? it's way beyond my VBA skills right now

Chris


----------



## IOANNIS

TO Jay Petrulis
I check my macro and is ok ...

so where is the error .. to me or to you ..

No .. we are both right ...

Look what happens ..

go to 

http://www.mrexcel.com/challenge.shtml

Mrexcel give two sets of numbers

one with "." and one with ","

my version of Excel requires the decimal place to be a comma so i used the second set ...

but all the numbers are the some except the  83,6 and the 507,8

895,39
83,6
280,71
1021,70
219,10
1587,52
507,8
628,89

second set

which are 83.06 and 507.08

first set

895.39
83.06
280.71
1021.7
219.1
1587.52
507.08
628.89

the difference is 0.54 and 0.72

so 4556.38 + 0.54 =4556.92 

so 4555.66 + 0.54 + 0.72 =4556.92 

the solutions with these two numbers had sum 4556.92 with the second set, the set i used but with the fisrt have other sums which depends if the solution use one of them or both ...


----------



## Jay Petrulis

> On 2002-08-18 02:48, Chris Davison wrote:
> Jay,
> 
> just a lateral thought :
> 
> we could reduce those 200 years down to a single year *if* we were able to incorporate into the code a method for splitting up the search into 200 different spreadhseets, sent to 200 volunteers, or a month if you split it down amongst 4,000
> 
> (not practical for accounts departments, yes, but let's forget abotu that, we're just interested in finding the solutions)
> 
> how easy would it be to slice the search up into 4,000 manageable segments - each segment being the next set to be searched when the spreadsheet was next copied and ran ?
> 
> surely it's possible to do this and then have the code email it's particular segment set of solutions back to the original person or project leader
> 
> [I'd been thinking about this along the lines of the SETI project where rather than have a single computer trying to analyse zillions of bits of info : packets of info are farmed out to volunteers across the world who each analyse their own little segment then post their results back]
> 
> I'm not saying we *need* 4,000 volunteers, what we need is the code that could do this.....
> 
> I don't know much about arrays in VBA, but know a simple way to allocate ranges of permutations based on their "binary address" (for want of a better way to describe it).... this would allow a block of 10,000,000 combinations to be checked easily
> 
> but if we could get the next version of the spreadhseet to check the next 10,000,000 combos, etc etc, that would work.....
> 
> any thoughts anyone ? it's way beyond my VBA skills right now
> 
> Chris



Hi Chris,

The partitioning of the problem will work, but that won't really help unless somebody comes up with a way to break Combin(53,12), for instance, into smaller parts.

C(53,1...7) shouldn't take too long, but the cap at 21 would take an enormous amount of time using a brute force approach.

After taking a closer look at the problem and writing a routine that will at least give me a benchmark, I am now more than ever convinced that the optimal non-Solver approaches are those of Ionnis and Sharad K., although we'll have to wait until Ioannis posts the routine.

Sharad and Ioannis are on the right track for large data sets....

Suppose that we find a solution 1,2,3,4,5 when selecting 5 items.  Any future paths should ideally eliminate that path from the run.

What I am referring to here is that when you are searching for solutions of 6 numbers, you should never even get to 

1,2,3,4,5,6
or 
1,2,3,4,5,7 and so forth.

When you carry this out further, that entire solution path removes a ton of unnecessary searches.

I haven't quite figured out how Sharad's recersion works, but that approach seems to work really well.  It appears that the routine starts with a pivot and exhausts the solutions for that single entry.  Then, it removes the number from the set and continues.  Over time, the original set of 54 is reduced, which really speeds things up.

Currently, all I have is an incomplete iteration method.  The recursion routine I already posted goes through the combinations 3 times (!!!!!), so it is unworkable.  My current thinking is along the lines of Sharad K.'s and also using multidimensional arrays to visualize the solution paths.  That is still only a concept right now with no implementation.

OK, anybody up for any side bets on the winner?  We've yet to hear from Damon Ostrander (my $$$ is on him if he jumps in  ).  Sharad and Ioannis are at the top right now.  Mark W. and the Solver routines have to be considered top contenders, too.  Jeffrey Chin's random search is along the lines of Tim C.'s first post in the newsgroup thread that started it all. Tim C. has some reservations about this, though.  

We still don't know if others have already offered great stuff privately.

Of additional interest, especially with the Solver-type solutions, check out the following thread

http://groups.google.com/groups?hl=...008e.0202121923.69589444%40posting.google.com

Dana DeLouis, a MS MVP, is terrific with the Solver and Harlan Grove makes some nice posts about this problem type.

_________________
Bye,
Jay

EDIT:  Forgot to add some reservations about the "winning" entry -- the processing power needed to exhaustively search all the combinations is beyond the scale of anything but a distributed computer system or a supercomputer, so if anybody comes up with a guarantee that all the answers are available within a few hours, I will be a bit disbelieving.  

Also, to solve for 1 or 2 chosen at a time (or 52 or 53) doesn't require any VBA.  1 is obvious.  For two, type the values in column A (starting at a1).  Name this range DataRange or such.  In C1, type the target value and name the cell GoalValue.

in B1, enter the following
=MATCH(ROUND(GoalValue-A1,2),DataRange,0)

and copy down the list.  Any non-error values will indicate the row of a matched pair.  Duplicates might mess this a bit, but this is relatively easy to get the case of n choose 2.
This message was edited by  Jay Petrulis on 2002-08-20 12:57


----------



## IOANNIS

.. I re check my macro and work fine (i use the second set of numbers) ..

.. I checked it with small amout of numbers, from 10 to 16 and i dint find any bugs

-16 NUMBER TEST-

I use 16 numbers from 1 to 16
Total combinations 2^16-1=65.535
check =34
Total solutions found =306

Then with simbly brute force macro I found all the 65535 combinations and their sums
I put Autofilter on columns and count the combinations with sum equal to 34 (count=306)

.. If it works with from 16 (10, 12 …) why not with 53 ?

So, is my computer so fast or my macro is too smart ?, that found all solutions in less than a week !!!

lets find out ...

My macro is based on brute force method but with some ways off rejecting compinations, (some mention by Jay Petrulis Above)

I put a counter in my macro to find how many combinations checks ..
After an hour the counter was at 384.030.162 and find 10.014 solutions

the  results for a day is  9.216.723.888 and 240.336 solutions

if i dont reject any combination the time needed is 2^53 -1 

total =2^53 -1 =9.007.199.254.740.990

total/9.216.723.888/365=2677 years

but the macro reject compinations ..

.. the computer check 384.030.162/hour but the solutions varies from 6000 to 15000 (or more)per hour ..

In one day the find minimum 72.000 solutions

The first Solutions come is a second which was the 
"1 2 3 4 5 6 7 8 9 10 11 12 13 15 18 27 29 45"
 afher 62407 compinations 

the solution's serial is 790.190.095.890

(with solution's serial i mean that the first combination is "1" serial 1, second combinations "1 2"=serial 2, combinations "53"=9.007.199.254.740.990 (the last, i maded a macro to find solution's serial number)

it reject 790.190.095.890-62407=790.190.033.483 !!!

the 72.000 solution is
" 1 2 4 5 7 9 11 12 13 15 29 31 44 45"
with serial =1.311.442.473.057.290

in one day reach the 1.311.442.473.057.290
combination  

to reach all

9.007.199.254.740.990/1.311.442.473.057.290=6,87 days

My macro made less than 6,87 (3 or 4 )days because we compute this time with the worst speed of solutions, 6000/hour (not the best 15000/hour)  ...........


----------



## IOANNIS

some correction to above ..

if the macro finds 72.000 solutions per day
3000/hour, 3000*24=72000 per day ..
the 72000th solutions is
"1 2 4 5 7 9 11 12 13 15 29 31 44 45" 
with serial =1.311.442.473.057.290 

in one day reach the 1.311.442.473.057.290 
combination 

to reach the last solutions

9.007.199.254.740.990/1.311.442.473.057.290=6,87 days 

My macro made less than 6,87 (3 or 4 )days because we compute this time with speed 3000/hour
But the speed varies from 6000 to 15000 (and more)per hour for the first numbers 01, 02, 03, which have 593968 solutions, 83% of total solutions and 7.881.299.347.898.370
compinations , 87,5 % of total.


01- 77,74 has 322.151 solutions  
02- 83,60 has 175.825 solutions  
03- 89,40 has  95.992 solutions 
04-116,14 has  51.315 solutions  
05-126,69 has  28.659 solutions  
06-144,77 has  16.142 solutions  
...
..
.

01- 77,74 has 4.503.599.627.370.500 compinations
02- 83,60 has 2.251.799.813.685.250 compinations
03- 89,40 has 1.125.899.906.842.620 compinations
04-116,14 has 	562.949.953.421.312 compinations
05-126,69 has 	281.474.976.710.656 compinations
06-144,77 has 	140.737.488.355.328 compinations

...  
..
.


----------



## bigbob

Hi,


----------



## bigbob

Hi,Don't know if this is useful but if you just look at the rightmost digit of each invoice then can only be 0 to 9 .The sum of a valid combination of these must add to rightmost digit of wanted total 
3 end in 0
3 end in 1
9 end in 2
6 end in 3
5 end in 4
5 end in 5
2 end in 6
8 end in 7
8 end in 8
5 end in 9
Taking invoices that end in 2 as example this will generate 0,2,4,6 or 8 as last digit dependant upon how many are in total .Doing same for other final digits gives different patterns and combining all possibilities gives final number of combinations of just over 1 x 10^6
This could be done by brute force
Hope this is understandable and hope it helps
Big bob


----------



## bigbob

Hi,Don't know if this is useful but if you just look at the rightmost digit of each invoice then can only be 0 to 9 .The sum of a valid combination of these must add to rightmost digit of wanted total 
3 end in 0
3 end in 1
9 end in 2
6 end in 3
5 end in 4
5 end in 5
2 end in 6
8 end in 7
8 end in 8
5 end in 9
Taking invoices that end in 2 as example this will generate 0,2,4,6 or 8 as last digit dependant upon how many are in total .Doing same for other final digits gives different patterns and combining all possibilities gives final number of combinations of just over 1 x 10^6
This could be done by brute force
Hope this is understandable and hope it helps
Big bob


----------



## mdfh

bigbob

But won't doing that prove that it is possible that each invoice could form part of the total?
So on that basis that would mean that no invoice could be eliminated.


----------



## MK

Here are some of the solutions...I have limited the number of invoices to six. And there are quite a few that add up to the required amount.

2487.85	911.45	796.76	144.77	126.69	89.4
2487.85	691.83	630.92	440.93	160.62	144.77
1842.59	895.39	796.76	691.83	185.58	144.77
2487.85	722.73	698.27	346.35	185.58	116.14
1587.52	978.53	925.39	789.77	192.65	83.06
2487.85	680.23	673.47	444.98	192.65	77.74
2487.85	789.77	538.64	456.68	194.58	89.4
2487.85	796.76	722.73	228.31	194.58	126.69
2487.85	764.18	538.64	444.98	194.58	126.69
1587.52	983.98	895.39	789.77	222.52	77.74
1842.59	983.98	718.32	628.89	222.52	160.62
2487.85	925.39	441.43	329.17	228.31	144.77
2487.85	796.76	680.23	280.71	228.31	83.06
1842.59	925.39	858.97	507.08	228.31	194.58
2487.85	691.83	680.23	280.71	230.72	185.58
1842.59	925.39	895.39	456.68	244.22	192.65
2487.85	862.12	538.64	346.35	244.22	77.74
1587.52	983.98	978.53	507.08	280.71	219.1
1587.52	925.39	911.45	718.32	324.84	89.4
1842.59	789.77	764.18	673.47	409.17	77.74
1842.59	862.12	691.83	673.47	409.17	77.74
1842.59	983.98	628.89	441.43	440.93	219.1
1842.59	895.39	722.73	538.64	441.43	116.14
1842.59	722.73	698.27	673.47	542.12	77.74
1587.52	983.98	698.27	628.89	542.12	116.14
1842.59	718.32	680.23	628.89	542.12	144.77
1021.7	983.98	911.45	789.77	630.92	219.1
1587.52	911.45	789.77	680.23	365.43	222.52
1842.59	978.53	630.92	441.43	440.93	222.52
1842.59	1021.7	630.92	589.18	244.22	228.31
983.98	978.53	862.12	796.76	589.18	346.35
1842.59	858.97	542.12	538.64	409.17	365.43
1587.52	925.39	680.23	589.18	409.17	365.43
1842.59	698.27	673.47	456.68	444.98	440.93
1021.7	911.45	895.39	698.27	589.18	440.93
1021.7	911.45	862.12	691.83	628.89	440.93
1021.7	911.45	789.77	764.18	628.89	440.93
983.98	789.77	764.18	698.27	691.83	628.89

p.s. I haven't tried solving this problem using Excel yet, this is using SQL. If anybody is interested in knowing the solution, let me know.
This message was edited by  MK on 2002-08-22 14:59


----------



## Jay Petrulis

> On 2002-08-22 14:57, MK wrote:
> Here are some of the solutions...I have limited the number of invoices to six. And there are quite a few that add up to the required amount.
> 
> ---snip solutions---
> 
> p.s. I haven't tried solving this problem using Excel yet, this is using SQL. If anybody is interested in knowing the solution, let me know.
> This message was edited by  MK on 2002-08-22 14:59



Hi,

Go ahead and post your solution/routine.  Who cares if it is in SQL?  You may have ideas that others can take and transform into Excel.  If it works, it is valuable on its own, too.


----------



## IOANNIS

Hi Jay Petrulis

Finally my macro had an error in how a variable pass its value (by value or by reference) which rejects solutions not combinations.The test with 16 numbers was ok but the numbers are integers ...

...i corrected it and i made some other changes too.I have no idea how to reject much more unusefull combinations, every attempt i made had as result of lost combinations or solutions, but the speed now is 20.000 to 38.500 solutions per hour !!!

I Replace the set of numbers with the first one and  i recalc the solutions ..

The macro finish after 32 hours !!!

The total solutions of the first set is 747.003 (747.003/32=23344/hour)

I stoped trying to improve the macro but i will continue to check it for errors and until the month ends i will post it ...

Perhaps i recalc again with the second set of numbers to find the solutions that rejects before the correction.
...What is 32 hours of my computer's life...


----------



## IOANNIS

...My computer is PIII 800Mhz, 512Mb ram ...


----------



## Sharad Kothari

I have updated the code to add additional filter to speed the things and incorporated other cosmetic changes. 

To explain the logic of the flow, the invoices are first sorted in ascending order and all the combinations are logically arranged is such a way that the highest values appear first and before other values. For e.g.

[ ]
.
.
53
53 52
53 52 51
.
53 52 51 50 ….. 3 2 1
53 52 50
.
53 13
53 13 12
.
53 1
52

[/ ]

The first filter, which was available in the code which I earlier posted, is that if the sum of any combinations is greater than the "Target Sum" then rest of the combination starting with that combination is skipped. For e.g. the sum of invoice number (sorted in ascending order) 53, 52, 51 (2487.85,1842.59,1587.52) is 5917.96 which is greater than target sum 4556.92 therefore rest of the combinations  starting with 53, 52, 51 would be skipped and logic flows to next combination which is 53 52 50.

The second filter is that if the sum of balance invoices is less than (“Target Sum” – sum of current combination) than such invoices are skipped. For e.g. the sum of invoices 53 and 1 to 13 is 4529.01 which is less than the “Target Sum” therefore all combinations starting with 53 and with next invoice of 13 or less are skipped. In the above example the code will be skipped to combination starting with 52.

The next problem is how to store the successful combinations as the size of significantly high. I first thought of storing the sequence number of the successful combination. I also developed two functions in this regard for returning combination from a sequence number and for returning sequence number from a combination. However, since precision number limit for Excel is 15 digit, I could not figure out how to store the exact sequence number.  Therefore, I combined the invoice number of successful invoices and stored into a single cell. For e.g. the first successful combination generated by the code is stored as:

_53_50_30_13_4_2_1

I have used he function “Combinations” to return the array of invoices from the above.

The code is given below. To use the code, simply paste in a module. And run the procedure “Main”. 

The speed of the code is significantly better than the code I last posted. It throws more than 1200 successful results within the first minute. The speed than settles around 45k/50k per hour.

if the code is stopped, than subsequently it can be resumed from that point only - from the last successful combination.




		Code:
__


Option Base 1
Option Explicit

Const cRangeSuccess = "B3"
Const cRangeTime = "B4"
Const cRangeLastComb = "B2"
Const cStartColumn = 4
Const cRefreshDelay = 10 'in seconds
Dim dSuccessCount As Double
Dim sLastComb As String
Dim lCurTime As Long

Dim aStack() As Integer
Dim lCurRow As Long, nCurCol As Integer
Dim aInvoice() As Currency
Dim curTarget As Currency

Sub Main()

Dim sLastCombNo As String
Dim nP1 As Integer, nP2 As Integer
Dim nCurStackElement As Integer
Dim n As Integer, m As Integer
Dim nCurInvoice As Integer, curBalanceSum As Currency
Dim rngTmp As Range

Application.ScreenUpdating = False

lCurTime = Timer()

Sheets("Sheet1").Activate

SetValues 'store all invoices to array aInvoice and sort in ascending order

ReDim aStack(UBound(aInvoice)) 'Stack contains the pointer to array of invoices

sLastCombNo = Range(cRangeLastComb)

If sLastCombNo = "" Then 'Start fresh
    
    SetupWS

    Range(cRangeTime) = 0

    lCurRow = 1
    nCurCol = cStartColumn

    For n = 1 To UBound(aInvoice)
        curBalanceSum = curBalanceSum + aInvoice(n)
    Next

    SearchTarget curTarget, curBalanceSum, UBound(aInvoice), 0

Else 'Continue

    nCurCol = cStartColumn + Cells(1, cStartColumn).CurrentRegion.Columns.Count - 1
    Set rngTmp = Cells(1, nCurCol).EntireColumn.Find("")
    If Not rngTmp Is Nothing Then
        lCurRow = rngTmp.Row
    Else
        lCurRow = 1
        nCurCol = nCurCol + 1
    End If

    dSuccessCount = Range(cRangeSuccess)

    'Update stack from the Last Combination No.
    Do
        nP1 = IIf(nP2 = 0, 1, nP2)
        nP2 = InStr(nP1 + 1, sLastCombNo, "_")
        nCurStackElement = nCurStackElement + 1
        If nP2 > 0 Then
            aStack(nCurStackElement) = Val(Mid(sLastCombNo, nP1 + 1, nP2 - nP1 - 1))
        Else
            aStack(nCurStackElement) = Val(Mid(sLastCombNo, nP1 + 1))
        End If
        curTarget = curTarget - aInvoice(aStack(nCurStackElement))
    Loop While nP2 > 0

    For n = nCurStackElement To 1 Step -1
        'till the last value of stack i.e [aStack(nCurStackElement] the combinations
        'has already been tested. Next in order would be invoice number of last value of stack minus 1
        nCurInvoice = aStack(n) - 1
        curTarget = curTarget + aInvoice(aStack(n))
        aStack(n) = 0
        For m = nCurInvoice To 1 Step -1
            curBalanceSum = curBalanceSum + aInvoice(m)
        Next
        SearchTarget curTarget, curBalanceSum, nCurInvoice, n - 1
    Next
End If

Application.ScreenUpdating = True

End Sub

' Main Recursive Routine
Sub SearchTarget(curTarget As Currency, curBalanceSum As Currency, nCurInvoice As Integer, nCurStackElement As Integer)
Dim curRevisedTarget As Currency, curRevisedBalanceSum As Currency
Dim n As Integer, m As Integer
Dim lDelay As Long 'Edit 26 Aug


If curBalanceSum >= curTarget Then
    curRevisedBalanceSum = curBalanceSum
    
    For n = nCurInvoice To 1 Step -1
        
        curRevisedBalanceSum = curRevisedBalanceSum - aInvoice(n)

        If aInvoice(n) = curTarget Or Timer() - lCurTime > cRefreshDelay Then
            
            m = 1
            sLastComb = ""
            Do While m<= UBound(aStack) And aStack(m) > 0
                sLastComb = sLastComb & "_" & Trim(Str(aStack(m)))
                m = m + 1
            Loop
            sLastComb = sLastComb & "_" & n
                     
            If aInvoice(n) = curTarget Then
                dSuccessCount = dSuccessCount + 1
                Range(cRangeLastComb) = sLastComb
                Cells(lCurRow, nCurCol) = sLastComb
                Range(cRangeSuccess) = dSuccessCount
                lCurRow = lCurRow + 1
                If lCurRow > ActiveSheet.Rows.Count Then
                     lCurRow = 1
                     nCurCol = nCurCol + 1
                End If
            End If

            lDelay = Timer() - lCurTime '-> Edit 26-Aug
            
            If lDelay > cRefreshDelay Or lDelay< 0 Then ' Edit 26-Aug
                Range(cRangeTime) = Range(cRangeTime) + IIf(lDelay< 0, cRefreshDelay, lDelay) 'Edit 26-Aug
                Application.ScreenUpdating = True
                Application.ScreenUpdating = False
                lCurTime = Timer()
            End If
        
        End If
    
          
        If aInvoice(n)< curTarget And n > 1 Then
            curRevisedTarget = curTarget - aInvoice(n)
            nCurStackElement = nCurStackElement + 1
            aStack(nCurStackElement) = n
            SearchTarget curRevisedTarget, curRevisedBalanceSum, n - 1, nCurStackElement
            aStack(nCurStackElement) = 0
            nCurStackElement = nCurStackElement - 1
        End If

    Next

End If

End Sub

Sub SetValues()

ReDim aInvoice(54)
aInvoice(1) = 895.39
aInvoice(2) = 83.06
aInvoice(3) = 280.71
aInvoice(4) = 1021.7
aInvoice(5) = 219.1
aInvoice(6) = 1587.52
aInvoice(7) = 507.08
aInvoice(8) = 628.89
aInvoice(9) = 222.52
aInvoice(10) = 192.65
aInvoice(11) = 194.58
aInvoice(12) = 764.18
aInvoice(13) = 680.23
aInvoice(14) = 244.22
aInvoice(15) = 89.4
aInvoice(16) = 862.12
aInvoice(17) = 1842.59
aInvoice(18) = 329.97
aInvoice(19) = 444.98
aInvoice(20) = 630.92
aInvoice(21) = 440.93
aInvoice(22) = 324.84
aInvoice(23) = 978.53
aInvoice(24) = 144.77
aInvoice(25) = 230.72
aInvoice(26) = 456.68
aInvoice(27) = 126.69
aInvoice(28) = 2487.85
aInvoice(29) = 515.11
aInvoice(30) = 911.45
aInvoice(31) = 983.98
aInvoice(32) = 329.17
aInvoice(33) = 673.47
aInvoice(34) = 409.17
aInvoice(35) = 228.31
aInvoice(36) = 796.76
aInvoice(37) = 116.14
aInvoice(38) = 858.97
aInvoice(39) = 718.32
aInvoice(40) = 346.35
aInvoice(41) = 542.12
aInvoice(42) = 589.18
aInvoice(43) = 789.77
aInvoice(44) = 185.58
aInvoice(45) = 538.64
aInvoice(46) = 441.43
aInvoice(47) = 925.39
aInvoice(48) = 698.27
aInvoice(49) = 5465.45
aInvoice(50) = 160.62
aInvoice(51) = 722.73
aInvoice(52) = 691.83
aInvoice(53) = 77.74
aInvoice(54) = 365.43

ASort aInvoice

curTarget = 4556.92

End Sub




Sub ASort(ByRef aArray, Optional nOrder As Integer)

' Sort Single Dimenstion Array

Dim nLength As Integer, nJump As Integer, nUpper As Integer
Dim nLower As Integer
Dim bFinished As Boolean
Dim vntTmp As Variant

nLength = UBound(aArray)
nJump = 1

Do While nJump<= nLength
    nJump = nJump * 2
Loop

Do While nJump > 1
    nJump = (nJump - 1)
    Do
        bFinished = True
        For nUpper = 1 To nLength - nJump
            nLower = nUpper + nJump
            If (nOrder >= 0 And aArray(nUpper) > aArray(nLower)) _
             Or nOrder< 0 And aArray(nUpper)< aArray(nLower) Then
                vntTmp = aArray(nUpper)
                aArray(nUpper) = aArray(nLower)
                aArray(nLower) = vntTmp
                bFinished = False
            End If
        Next nUpper
    Loop Until bFinished
Loop

End Sub

Sub SetupWS()

    With Range("A1")
        .Value = "Summary"
        .Font.Bold = True
        .Font.Underline = True
    End With
    
    With Range(cRangeLastComb)
        .Offset(0, -1) = "Last_Comb"
        .Offset(0, -1).EntireColumn.ColumnWidth = 14
        .ShrinkToFit = True
        .EntireColumn.ColumnWidth = 35
    End With
    
    With Range(cRangeSuccess)
        .Offset(0, -1) = "Success"
        .HorizontalAlignment = xlCenter
    End With
    
    With Range(cRangeTime)
        .Offset(0, -1) = "Seconds"
        .HorizontalAlignment = xlCenter
        .NumberFormat = "0"
        .Offset(1, -1) = "Success/Hour"
        .Offset(1, 0).Formula = "=ROUND(" & cRangeSuccess & "/" & cRangeTime & "*3600/1000,0)*1000"
        .Offset(1, 0).HorizontalAlignment = xlCenter
    End With
    
End Sub

Function Combinations(sCurComb As String)

Dim nP1 As Integer, nP2 As Integer
Dim nCurStackElement As Integer

SetValues
Dim aStackValues() As Currency
ReDim aStackValues(UBound(aInvoice))

Do
    nP1 = IIf(nP2 = 0, 1, nP2)
    nP2 = InStr(nP1 + 1, sCurComb, "_")
    nCurStackElement = nCurStackElement + 1
    If nP2 > 0 Then
        aStackValues(nCurStackElement) = aInvoice(Val(Mid(sCurComb, nP1 + 1, nP2 - nP1 - 1)))
    Else
        aStackValues(nCurStackElement) = aInvoice(Val(Mid(sCurComb, nP1 + 1)))
    End If
Loop While nP2 > 0

Combinations = aStackValues
End Function



EDIT - Edited to correct seconds counter which stooped at midnight as Timer function is reset. - Sharad Kothari, 26August
This message was edited by  Sharad Kothari on 2002-08-25 22:41


----------



## Sharad Kothari

I have finished running my code which I recently posted; stoping and restarting sevaral times.

The total solutions are 747004 which is one more than mentioned by IOANNIS, although there is all probability of counting error on my part as I interrupted the execution of program sevaral times. The estimated time is about 24 hours at a speed of about 30K. This was on my laptop - PIII and 128MB Ram.

I tried the same code on my desktop - P4 and 256MB Ram -  for about 15 minutes and the average speed was 85K per hour. Therefore, I expect to process all the combinations on my desktop in less than 10 hours, which I will try tonight.

Some additional Oservations:
=> After storing all the 747K results, the file size goes up to 40MB. 
=> The speed can be increased furhter, if only summary of result is maintained instead of storing all the result.
=> Even while storing all the results, the speed can be increased slightly by increasing the RefreshDealy. 
=> In my first code, I was using Double Data type, to store the Invoices, which was skipping a lot of successful combination due to insignificant decimal digit. After converting the data type to Currency, the code is working properly.
=> Just by applying one additional filter, the speed of the code has increased significantly. I do not have any clue of any further filter, but if such further filter could be applied, it should further improve the speed.


----------



## Sharad Kothari

I have finished processing the code on my P4-256MHz. The possible combinations are 747003 and it took 9Hours and 24 Minutes at an average speed of 79K per hour to process all the invoices.


----------



## richrr31

I don't think the relevance of the issue is worth discussion.  Who cares? It's just fun to try and come up with a solution.....Here is my stab at it, pseudocode only(I have real work to do!) 

1. T=The goal in $    N=The number of different entries
2. List all of the values in descending order.
3. If there is one equal to the goal(T) copy that cell to a different sheet. (To obtain the formula of that cell later.)
4. Remove any values that are more than T(they are useless).
5. On a different sheet, make a pyramid, adding the lesser values to the ones greater.

i.e. If the numbers are 4,3,2,1 and the goal is 8, then
4
3
2
1 would be the list, and the pyramid on the next sheet would look like this:

[4+3] [4+2] [4+1]
[3+2] [3+1]
[2+1]

6. Loop back to step 2, making the list separate by rows:
The new list would look like this:

[4+3]
[4+2]
[4+1]
------
[3+2]
[3+1]
------
[2+1]

7. Back to step 3, moving any that are the exact value to a different page(so we can get the formula later), and remove any that are greater than T. The difference however, is to list them separarately by row.

8. Remove all values which can no longer be added to, then perform step 5 again:

[4+3+2] [4+3+1]
[4+2+1]
-----------
[3+2+1]
9. Once again, we would make a new list, separating:
[4+3+2] Deleted(Too Much)
              Moved to other sheet:[4+3+1]
---------
[4+2+1] Deleted(no more adds)
---------
[3+2+1] Deleted (no more adds)

10. Move all values equal to T to the other sheet(to get the formula).  Delete ones that are over, and remove ones that can't be added to:
In this case there aren't any.....By needing to look for multiple values, this process will take longer than it should.  This method, however, checks every possible combo, but never performs an unnecessary calculation(If a combo is too much or exact, no more additions are performed).

When everything is all said and done, the good formulas are located on a separate sheet, uncover the formulas, and you have your answer.

Thanks!


----------



## Damon Ostrander

Solver can be used to find solutions also. The following solution, different from the ones posted so far, took Solver a couple minutes:

895.39
507.08
222.52
192.65
89.40
230.72
911.45
329.17
673.47
228.31
116.14
160.62

To set up solver to do this problem I just used a binary 0 or 1 multiplier on each invoice amount and used Solver to drive the sum of the multiplied amounts to the target (payment received) amount.

When there are multiple solutions, as in this case, different solutions can be obtained with Solver by using different initializations. Of course, Solver has the same problem all the others have--if the list is big it can take a LONG time (is a billion years too long?). I believe Solver's efficiency comes mainly from the fact that it is compiled code--probably written in C.

Cheers to all for the very interesting problem, discussion, and proposed solutions.


----------



## Jay Petrulis

Hi,

Here is a total brute force method, which will likely be very slow.

I named the ranges with the data and the target, and commented out the methods to prompt for them.

I think that the routines by Ioannis and Sharad K. are much nicer (although we've yet to see the code from Ioannis), and the Solver solutions are more elegant than this for sure.



		Rich (BB code):
__


Option Explicit
Dim fn As WorksheetFunction
Public HoldingArray() As Variant

Sub MrExcelChallengeAug2002()
Dim wks As Worksheet
Dim n As Integer, k As Integer, a As Integer, i As Long
Dim Data_Set As Range, Target_Val As Double, Data_to_Use
Dim arrsize As Long

Set fn = Application.WorksheetFunction
Set Data_Set = Range("DataList") 'Application.InputBox("Enter the range of values", , , , , , , :cool:
Target_Val = fn.Round(Range("GoalValue"), 2) 'fn.Round(Application.InputBox("Enter the target value", , , , , , , 9), 2)
Set wks = Worksheets.Add

Data_to_Use = FindValues(Data_Set, Target_Val)
Set Data_Set = Nothing
n = UBound(Data_to_Use) - LBound(Data_to_Use) + 1
'k = fn.Min(7, Find_Max_K(n, Data_to_Use, Target_Val))
k = Find_Max_K(n, Data_to_Use, Target_Val)

ReDim HoldingArray(1 To k)
For a = 1 To k
    Application.StatusBar = a
    If a<= 12 Then
        HoldingArray(a) = Find_Possible(Data_to_Use, Target_Val, n, a)
    ElseIf a<= 18 Then
        HoldingArray(a) = Find_Possible2(Data_to_Use, Target_Val, n, a)
    Else
        HoldingArray(a) = Find_Possible3(Data_to_Use, Target_Val, n, a)
    End If
    arrsize = UBound(HoldingArray(a)) - LBound(HoldingArray(a)) + 1
    If arrsize<= 65536 Then
        wks.Cells(1, a).Resize(arrsize, 1) = fn.Transpose(HoldingArray(a))
    End If
Next a
Application.StatusBar = False
End Sub

Function FindValues(Data_Set, ByVal Target_Val As Double)
Dim FirstCounter As Integer, SecondCounter As Integer
Dim Cell As Range, NumNegative As Integer, SecondSet
Dim x As Integer

If TypeName(Data_Set) = "Range" Then
    ReDim FirstSet(1 To Data_Set.Cells.Count) As Double
    For Each Cell In Data_Set
        If IsNumeric(Cell) And Not IsEmpty(Cell) Then
            FirstCounter = FirstCounter + 1
            FirstSet(FirstCounter) = Cell
            If Cell< 0 Then
                NumNegative = NumNegative + 1
            End If
        End If
    Next Cell
ElseIf TypeName(Data_Set) = "Variant()" Then
    FirstSet = fn.Transpose(Data_Set)
    FirstCounter = UBound(FirstSet) - LBound(FirstSet) + 1
Else
    FindValues = CVErr(xlErrNum)    ' Exit function if values are unworkable
    Exit Function
End If

'''  Exit function if no valid entries on first pass
If FirstCounter = 0 Then
    FindValues = CVErr(xlErrNum)
    Exit Function
End If
'''''''''''''SECOND PASS''''''''''''''''''''''''''''
'
'''  This should allow us to start with the minimum number of
'''  choices to loop through.  If there are no negative numbers,
'''  this will eliminate any impossible values, i.e., > Target_Val.
'''  Note:  It is not possible to eliminate duplicated numbers.
'''  Suppose the out-of-balance is 375.00 and there are 3 entries
'''  of 125.00.  Dropping two duplicates will drop a solution.

If NumNegative Then
    SecondSet = FirstSet ' can't eliminate if negatives exist
    SecondCounter = UBound(SecondSet) - LBound(SecondSet) + 1
Else
    ReDim SecondSet(1 To UBound(FirstSet) - LBound(FirstSet) + 1)
    For x = LBound(FirstSet) To UBound(FirstSet)
        If FirstSet(x)<= Target_Val Then
            SecondCounter = SecondCounter + 1
            SecondSet(SecondCounter) = FirstSet(x)
        End If
    Next x
    On Error Resume Next
        ReDim Preserve SecondSet(1 To SecondCounter)
    On Error GoTo 0
    Err.Clear
End If
'''  Exit function if no valid entries after second pass
If SecondCounter = 0 Then
    FindValues = CVErr(xlErrNum)
    Exit Function
End If
Call QuickSortVariants(SecondSet, LBound(SecondSet), UBound(SecondSet))
FindValues = SecondSet
End Function

Function Find_Max_K(nums, Data_Used, TargetValue) As Integer
Dim i As Integer, DataSum As Double
Set fn = Application.WorksheetFunction
    
For i = 1 To nums
    DataSum = DataSum + fn.Round(Data_Used(i), 2)
    If DataSum > TargetValue Then
        Find_Max_K = i - 1
        Exit Function
    End If
Next i
Find_Max_K = nums
End Function


Sub QuickSortVariants(vArray As Variant, inLow As Long, inHi As Long)
      
'''  Routine posted by Ivan F. Maola to MrExcel.com Message Board
'''  http://www.mrexcel.com/board/viewtopic.php?topic=16211&forum=2
'''  Original author unknown
'''  Comments deleted in code below


   Dim pivot   As Variant
   Dim tmpSwap As Variant
   
   Dim tmpLow  As Long
   Dim tmpHi   As Long
    
   tmpLow = inLow
   tmpHi = inHi
    
   pivot = vArray((inLow + inHi)  2)
  
   While (tmpLow<= tmpHi)
  
      While (vArray(tmpLow)< pivot And tmpLow< inHi)
         tmpLow = tmpLow + 1
      Wend
      
      While (pivot< vArray(tmpHi) And tmpHi > inLow)
         tmpHi = tmpHi - 1
      Wend

      If (tmpLow<= tmpHi) Then
      
         tmpSwap = vArray(tmpLow)
         vArray(tmpLow) = vArray(tmpHi)
         vArray(tmpHi) = tmpSwap
         
         tmpLow = tmpLow + 1
         tmpHi = tmpHi - 1
      End If
   
   Wend
  
   If (inLow< tmpHi) Then QuickSortVariants vArray, inLow, tmpHi
   If (tmpLow< inHi) Then QuickSortVariants vArray, tmpLow, inHi
  
End Sub


The above is the main driver routine and some auxiliary routines which find the minimum number of entries that you would need to evaluate.

It calls one of 3 routines, depending on the size of the number to select.

I have listed only one of them for space reasons.  The others are structured exactly the same way.

Sorry about the length of this...



		Rich (BB code):
__


Function Find_Possible(DataList, ByVal TargetValue As Double, _
ByVal elements As Integer, ByVal numselect As Integer)

Dim SolutionArray, Counter As Double, cnt As Double
Dim SumCounter As Integer, SubtotalSum1 As Double, SubtotalSum2 As Double
Dim SubtotalSum3 As Double, SubtotalSum4 As Double, SubtotalSum5 As Double
Dim SubtotalSum6 As Double, SubtotalSum7 As Double, SubtotalSum8 As Double
Dim SubtotalSum9 As Double, SubtotalSum10 As Double, SubtotalSum11 As Double
Dim SubtotalSum12 As Double, SubtotalSum13 As Double, SubtotalSum14 As Double
Dim SubtotalSum15 As Double, SubtotalSum16 As Double, SubtotalSum17 As Double
Dim SubtotalSum18 As Double, SubtotalSum19 As Double, SubtotalSum20 As Double
Dim SubtotalSum21 As Double
Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer, f As Integer
Dim g As Integer, h As Integer, i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, o As Integer, p As Integer, q As Integer, r As Integer
Dim s As Integer, t As Integer, u As Integer, v As Integer, w As Integer, x As Integer
Set fn = Application.WorksheetFunction

If numselect<= 6 Then ReDim SolutionArray(1 To fn.Combin(elements, numselect)) As String

Select Case numselect
    Case 0
        Counter = Counter + 1
        SolutionArray(1) = 0
    Case elements
        If fn.Sum(DataList) = TargetValue Then
            Counter = Counter + 1
            SolutionArray(1) = DataList
        End If
    Case Is > elements
        Counter = Counter + 1
        SolutionArray(1) = 0
    Case 1
        For a = 1 To elements
            If fn.Round(DataList(a), 2) = TargetValue Then
                Counter = Counter + 1
                SolutionArray(Counter) = DataList(a)
            End If
        Next a
    Case 2
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            If SubtotalSum1 = TargetValue Then
                Counter = Counter + 1
                SolutionArray(Counter) = DataList(a) & " | " & DataList(b)
            End If
        Next b: Next a
    Case 3
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements + 2 - numselect
        For c = b + 1 To elements
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            SubtotalSum2 = fn.Round(SubtotalSum1 + DataList(c), 2)
            If SubtotalSum1 > TargetValue Then GoTo Exit3_1
            If SubtotalSum2 > TargetValue Then
                GoTo Exit3
            ElseIf SubtotalSum2 = TargetValue Then
            Counter = Counter + 1
            SolutionArray(Counter) = DataList(a) & " | " & DataList(b) _
            & " | " & DataList(c)
            End If
        Next c
Exit3:
        Next b
Exit3_1:
        Next a

    Case 4
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements + 2 - numselect
        For c = b + 1 To elements + 3 - numselect
        For d = c + 1 To elements
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            SubtotalSum2 = fn.Round(SubtotalSum1 + DataList(c), 2)
            SubtotalSum3 = fn.Round(SubtotalSum2 + DataList(d), 2)
            If SubtotalSum1 > TargetValue Then GoTo Exit4_2
            If SubtotalSum2 > TargetValue Then GoTo Exit4_1
            If SubtotalSum3 > TargetValue Then
                GoTo Exit4
            ElseIf SubtotalSum3 = TargetValue Then
            Counter = Counter + 1
            SolutionArray(Counter) = DataList(a) & " | " & DataList(b) _
            & " | " & DataList(c) & " | " & DataList(d)
            End If
        Next d
Exit4:
        Next c
Exit4_1:
        Next b
Exit4_2:
        Next a

    Case 5
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements + 2 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b), HoldingArray(2), 0)) Then GoTo Exit5_3
            
        For c = b + 1 To elements + 3 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c), HoldingArray(3), 0)) Then GoTo Exit5_2
        
        For d = c + 1 To elements + 4 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d), _
            HoldingArray(4), 0)) Then GoTo Exit5_1
              
        For e = d + 1 To elements
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            SubtotalSum2 = fn.Round(SubtotalSum1 + DataList(c), 2)
            SubtotalSum3 = fn.Round(SubtotalSum2 + DataList(d), 2)
            SubtotalSum4 = fn.Round(SubtotalSum3 + DataList(e), 2)
            If SubtotalSum1 > TargetValue Then GoTo Exit5_3
            If SubtotalSum2 > TargetValue Then GoTo Exit5_2
            If SubtotalSum3 > TargetValue Then GoTo Exit5_1
            If SubtotalSum4 > TargetValue Then
                GoTo Exit5
            ElseIf SubtotalSum4 = TargetValue Then
            Counter = Counter + 1
            SolutionArray(Counter) = DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) & " | " & DataList(e)
            End If
        Next e
Exit5:
        Next d
Exit5_1:
        Next c
Exit5_2:
        Next b
Exit5_3:
        Next a

    Case 6
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements + 2 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b), HoldingArray(2), 0)) Then GoTo Exit6_4
        
        For c = b + 1 To elements + 3 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c), HoldingArray(3), 0)) Then GoTo Exit6_3
        
        For d = c + 1 To elements + 4 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d), _
            HoldingArray(4), 0)) Then GoTo Exit6_2
            
        For e = d + 1 To elements + 5 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) _
            & " | " & DataList(e), HoldingArray(5), 0)) Then GoTo Exit6_1
        
        For f = e + 1 To elements
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            SubtotalSum2 = fn.Round(SubtotalSum1 + DataList(c), 2)
            SubtotalSum3 = fn.Round(SubtotalSum2 + DataList(d), 2)
            SubtotalSum4 = fn.Round(SubtotalSum3 + DataList(e), 2)
            SubtotalSum5 = fn.Round(SubtotalSum4 + DataList(f), 2)
            
            If SubtotalSum1 > TargetValue Then GoTo Exit6_4
            If SubtotalSum2 > TargetValue Then GoTo Exit6_3
            If SubtotalSum3 > TargetValue Then GoTo Exit6_2
            If SubtotalSum4 > TargetValue Then GoTo Exit6_1
            If SubtotalSum5 > TargetValue Then
                GoTo Exit6
            ElseIf SubtotalSum5 = TargetValue Then
            Counter = Counter + 1
            SolutionArray(Counter) = DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) & " | " & DataList(e) & " | " & DataList(f)
            End If
        Next f
Exit6:
        Next e
Exit6_1:
        Next d
Exit6_2:
        Next c
Exit6_3:
        Next b
Exit6_4:
        Next a


    Case 7
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements + 2 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b), HoldingArray(2), 0)) Then GoTo Exit7_5
        
        For c = b + 1 To elements + 3 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c), HoldingArray(3), 0)) Then GoTo Exit7_4
        
        For d = c + 1 To elements + 4 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d), _
            HoldingArray(4), 0)) Then GoTo Exit7_3
            
        For e = d + 1 To elements + 5 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) _
            & " | " & DataList(e), HoldingArray(5), 0)) Then GoTo Exit7_2
        
        For f = e + 1 To elements + 6 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) _
            & " | " & DataList(e) & " | " & DataList(f), HoldingArray(6), 0)) Then GoTo Exit7_1
            
        For g = f + 1 To elements
        Application.StatusBar = a & "|" & b & "|" & c & "|" & d & "|" & e & "|" & f
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            SubtotalSum2 = fn.Round(SubtotalSum1 + DataList(c), 2)
            SubtotalSum3 = fn.Round(SubtotalSum2 + DataList(d), 2)
            SubtotalSum4 = fn.Round(SubtotalSum3 + DataList(e), 2)
            SubtotalSum5 = fn.Round(SubtotalSum4 + DataList(f), 2)
            SubtotalSum6 = fn.Round(SubtotalSum5 + DataList(g), 2)
            If SubtotalSum1 > TargetValue Then GoTo Exit7_5
            If SubtotalSum2 > TargetValue Then GoTo Exit7_4
            If SubtotalSum3 > TargetValue Then GoTo Exit7_3
            If SubtotalSum4 > TargetValue Then GoTo Exit7_2
            If SubtotalSum5 > TargetValue Then GoTo Exit7_1
            If SubtotalSum6 > TargetValue Then
                GoTo Exit7
            ElseIf SubtotalSum6 = TargetValue Then
            Counter = Counter + 1
            If Counter = 1 Then
                ReDim SolutionArray(1 To Counter) As String
            Else
                ReDim Preserve SolutionArray(1 To Counter) As String
            End If
            SolutionArray(Counter) = DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) & " | " & DataList(e) _
            & " | " & DataList(f) & " | " & DataList(g)
            End If
        Next g
Exit7:
        Next f
Exit7_1:
        Next e
Exit7_2:
        Next d
Exit7_3:
        Next c
Exit7_4:
        Next b
Exit7_5:
        Next a

    Case 8
        For a = 1 To elements + 1 - numselect
        For b = a + 1 To elements + 2 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b), HoldingArray(2), 0)) Then GoTo Exit8_6
        
        For c = b + 1 To elements + 3 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c), HoldingArray(3), 0)) Then GoTo Exit8_5
        
        For d = c + 1 To elements + 4 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d), _
            HoldingArray(4), 0)) Then GoTo Exit8_4
            
        For e = d + 1 To elements + 5 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) _
            & " | " & DataList(e), HoldingArray(5), 0)) Then GoTo Exit8_3
        
        For f = e + 1 To elements + 6 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) _
            & " | " & DataList(e) & " | " & DataList(f), HoldingArray(6), 0)) Then GoTo Exit8_2
            
        For g = f + 1 To elements + 7 - numselect
            If Not IsError(Application.Match(DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) _
            & " | " & DataList(e) & " | " & DataList(f) & " | " & DataList(g), HoldingArray(7), 0)) Then GoTo Exit8_1
        
        For h = g + 1 To elements
        Application.StatusBar = a & "|" & b & "|" & c & "|" & d & "|" & e & "|" & f
            SubtotalSum1 = fn.Round(DataList(a) + DataList(b), 2)
            SubtotalSum2 = fn.Round(SubtotalSum1 + DataList(c), 2)
            SubtotalSum3 = fn.Round(SubtotalSum2 + DataList(d), 2)
            SubtotalSum4 = fn.Round(SubtotalSum3 + DataList(e), 2)
            SubtotalSum5 = fn.Round(SubtotalSum4 + DataList(f), 2)
            SubtotalSum6 = fn.Round(SubtotalSum5 + DataList(g), 2)
            SubtotalSum7 = fn.Round(SubtotalSum6 + DataList(h), 2)
            If SubtotalSum1 > TargetValue Then GoTo Exit8_6
            If SubtotalSum2 > TargetValue Then GoTo Exit8_5
            If SubtotalSum3 > TargetValue Then GoTo Exit8_4
            If SubtotalSum4 > TargetValue Then GoTo Exit8_3
            If SubtotalSum5 > TargetValue Then GoTo Exit8_2
            If SubtotalSum6 > TargetValue Then GoTo Exit8_1
            If SubtotalSum7 > TargetValue Then
                GoTo Exit8
            ElseIf SubtotalSum7 = TargetValue Then
            Counter = Counter + 1
            If Counter = 1 Then
                ReDim SolutionArray(1 To Counter) As String
            Else
                ReDim Preserve SolutionArray(1 To Counter) As String
            End If
            SolutionArray(Counter) = DataList(a) & " | " & DataList(b) & " | " & DataList(c) & " | " & DataList(d) & " | " & DataList(e) _
            & " | " & DataList(f) & " | " & DataList(g) & " | " & DataList(h)
            End If
        Next h
Exit8:
        Next g
Exit8_1:
        Next f
Exit8_2:
        Next e
Exit8_3:
        Next d
Exit8_4:
        Next c
Exit8_5:
        Next b
Exit8_6:
        Next a

----snip lots of similar code---

End Select
    If Counter = 0 Then
        ReDim SolutionArray(1 To 1)
        SolutionArray(1) = "No solutions"
    Else
        ReDim Preserve SolutionArray(1 To Counter) As String
    End If
Find_Possible = SolutionArray
Erase SolutionArray
End Function


_________________
Bye,
Jay
This message was edited by  Jay Petrulis on 2002-08-31 07:26


----------



## IOANNIS

HI ALL !!
I am back (i was on holidays ..)

Finallly after a few changes my macros speed is about 41000/hour (in pentium III800, 512ram, excel 2000)

18 hours and 20 minutes !!

i email it to Mrexcel

I run the macro to another pentium III 800, 256ram, excel 2000, and the speed was after 0:02:25 min  87.244 !!!

somethins has my computer at home ..

..screen shot ..BRUTE_FORCE_FINAL.xlsABCDEFGH1177,740RES_01TOTALINVOCIES54123456789101112131415182034362283,060RES_02CHECK4556,92123456789101112131416192328383389,400RES_03MAX_SUM_NUMBERS211234567891011121315161718244544116,140RES_04MAX_CHECK_No5312345678910111213161728334055126,690RES_05CURRENTSHEET_SOLUTIONS351412345678910111213162226274466144,770RES_06TOTAL_SOLUTIONS_FOUND3514123456789101112131728364977160,620RES_07TOTALSHEETSOLUTIONS0123456789101112132531364188185,580RES_08MAX_RESUME_No012345678910111214152224274699192,650RES_09LASTSOLUTIONFOUNDorCOMBINATION1234567891011121416172133461010194,580RES_105312345678910111214202841441111219,100RES_11CHECKEDCOMBINATIONS=Print_No(E10;F4)123456789101112151620222427281212222,520RES_12TOTAL_COMBINATIONS9.007.199.254.740.990123456789101112151719202124361313228,310RES_13COMBINATION'S%DONE=(F11/F12)*10012345678910111217183437381414230,720RES_14MACRO'STIMESTART20:07:2712345678910111217212332491515244,220RES_15LASTFOUNDSOLUTION'STIME20:09:5212345678910111224262730421616280,710RES_16ELAPSEDTIME0:02:251234567891011131416181931481717324,840RES_17REMAINTIME=F19*((100-F13)/F13)1234567891011131418232830351818329,170RES_18LASTRESUMETIME0:00:0012345678910111314242633481919329,970RES_19TOTALCALCTIME0:02:2512345678910111315163738422020346,350RES_20SOLUTION'SSPEED/HOUR87.24412345678910111315182642462121365,430RES_2112345678910111318243135372222409,170RES_2212345678910111320212932432323440,930RES_2312345678910111415171946472424441,430RES_24123456789101114151720212226312525444,980RES_251234567891011141518212526452626456,680RES_261234567891011141617293546August 2002 Challenge

I must check it ...

Bye for now ...


----------



## Jay Petrulis

Hi Ioannis,

Nice job.  When the published results are available, I am confident that you and Sharad K. will take the top spots.  My routine won't get anywhere near your speed for sure.  

The Solver routines, if they can store prior solutions and elminate them from the next iteration, may be even faster, although there may be a chance that they won't exhaust all possibilities.

Kudos.


----------



## IOANNIS

Hi Jay

I checked my computer's speed and now is ok

I re run the macro and the final speed for
my computer is now 62300 solutions per hour
and 11 hours and 59 minutes !!

My macro olso support and negative numbers

I have check it with extreme sets off numbers,
such as o.oo numbers, all zeros, check=0, gives all compinations)

Zeros with negative and positive numbers and
if all numbers are equal (01=1, 02=1, 03=1, 04=1 ...) and all the tests was ok ..

Since I want to make a general brute force macro, the macro do not use any Excel's features and is easy to tranfered to another programming language sush as c++ for optimum speed

The Sharad Kothari macro is Excellent !
Good work Sharad


----------



## IOANNIS

... I will run the the Sharad Kothari macro on my computer to compare the speed ...

.. I will post the results when i am ready ..


----------



## IOANNIS

..after 9:20 minutes the Sharad Kothari macro has found 531553 solutions with speed 57000 per hour ..

.. With this speed the macro will finished after 4 hours with total execution time about 13 hours and 8 to 12 minutes ..


----------



## IOANNIS

..fINAL speed 55000/hour, 13 hours ang 37 minites..


----------



## IOANNIS

to MrExcel

I cant email the solutions xls ...

..

Your message did not reach some or all of the intended recipients.

      Subject:	August  Challenge Macro
      Sent:	31/8/2002 7:56 ìì

The following recipient(s) could not be reached:

mrexcel@ameritech.net on 31/8/2002 9:32 ìì
            This message is larger than the current system limit or the recipient's mailbox is full.  Create a shorter message body or remove attachments and try sending it again.
            < net3.planetnet.com #5.2.2>

...

I email only the macro xls ...

...


----------



## IOANNIS

..The Final Macro ..
This message was edited by  IOANNIS on 2002-09-03 01:15


----------



## IOANNIS

.. Something goes wrong .. with copy-paste ..

it replace the "8)" with a red face ??


----------



## IOANNIS

is there a bug on Colo's cool utility for displaying Excel Worksheet on the board ??


----------



## IOANNIS

BRUTE_FORCE_FINAL.xlsJKLM12August 2002 Challenge


----------



## IOANNIS

??


----------



## RichardS

Disable smilies on your post.

Richard


----------



## IOANNIS

.. THE FINAL MACRO ..
.. Module 1 ... 


		Code:
__


Dim INV() As Long
Dim CHECK As Long
Dim MAX_CHECK_INVS_No As Integer
Dim Sol As Long
Dim RESUME_No() As Integer
Dim RES_No As Integer
Dim RESUME_CALC As Integer
Dim MAX_RESUME_No As Integer
Dim AA As Long
Dim MAX_INVS As Integer
Dim MAX_DEPTH As Integer


Sub Challenge()

' SORTING
Columns("B:B").Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' CLEAR SOLUTION AREA

Columns("H:H").Select
Selection.ClearContents

Columns("M:P").Select
Selection.ClearContents

Cells(11, 4).Select
Selection.Activate

Cells(18, 6) = 0
Cells(7, 6) = 0
CHECK = Cells(2, 6) * 100

TOT_INV = Cells(1, 6)
ReDim INV(TOT_INV + 1)

MaxSum = 0

For i = 1 To TOT_INV
   INV(i) = Cells(i, 2) * 100
   MaxSum = MaxSum + INV(i)
Next

SUM_INV = 0

MAX_INVS = 0

For i = 1 To TOT_INV
   SUM_INV = SUM_INV + INV(i)
   If SUM_INV = CHECK Then MAX_INVS = i: Exit For
   If SUM_INV > CHECK Then MAX_INVS = i - 1: Exit For
Next

Cells(3, 6) = MAX_INVS

MAX_CHECK_INVS_No = 1
i = 1

For i = TOT_INV To 2 Step -1
    SUM_INV = INV(i)
    If SUM_INV = CHECK Then GoTo Exit_for
    For k = 1 To i
        SUM_INV = SUM_INV + INV(k)
        If SUM_INV <= CHECK Then
           GoTo Exit_for
        End If
    Next
Next

Exit_for:
MAX_CHECK_INVS_No = i

INV(MAX_CHECK_INVS_No + 1) = MaxSum

Cells(4, 6) = MAX_CHECK_INVS_No

Sol = 0
AA = 0

Cells(14, 6) = Time

'Application.ScreenUpdating = False

    Find_Sol 0, "", 0
    
    Cells(10, 5) = Str(MAX_CHECK_INVS_No)
    Cells(15, 6) = Time
    
Application.ScreenUpdating = True

End Sub
    
Sub Find_Sol(No_01 As Integer, NN_01 As String, SINVS_01 As Long)
 
For No_02% = No_01 + 1 To MAX_CHECK_INVS_No
    
    NN_02$ = NN_01 + Str(No_02%)
    SINVS_02& = SINVS_01 + INV(No_02%)
        
    If SINVS_02& > CHECK Then Exit For
    If SINVS_02& = CHECK Then
       Sol = Sol + 1
       If Sol > 65536 Then Sol = 1: COPY_SOLUTIONS
       
       Cells(Sol, 8) = NN_02$
       'Cells(10, 5) = NN_02$
       Cells(15, 6) = Time
                     
    End If
    
    If (SINVS_02& + INV(No_02% + 1)) > CHECK Then
          
       If (INV(No_02%) = INV(No_02% + 1)) Then GoTo END_LOOP
       
       MAX_No_01% = MAX_CHECK_INVS_No + 1
       No_02% = No_02% + 1
       
START_LOOP:
      
       CH_No% = MAX_No_01% - No_02%
       
       If CH_No% > 1 Then
          CH_No_m% = CH_No% / 2 + No_02%
      
          If (SINVS_01 + INV(CH_No_m%)) > CHECK Then
             MAX_No_01% = CH_No_m%
             GoTo START_LOOP
          End If
          If (SINVS_01 + INV(CH_No_m%)) = CHECK Then
             Sol = Sol + 1
             If Sol > 65536 Then Sol = 1: COPY_SOLUTIONS
             Cells(Sol, 8) = NN_01 + Str(CH_No_m%)
             Exit For
          End If
          If (SINVS_01 + INV(CH_No_m%)) < CHECK Then
             No_02% = CH_No_m%
             GoTo START_LOOP
          End If
        Else
          If CH_No% = 1 Then
             No_02% = MAX_No_01% - 1
             If (SINVS_01 + INV(No_02%)) = CHECK Then
                Sol = Sol + 1
                If Sol > 65536 Then Sol = 1: COPY_SOLUTIONS
                   Cells(Sol, 8) = NN_01 + Str(No_02%)
                  Exit For
                End If
             End If
             Exit For
          End If
       End If
END_LOOP:
    
    Find_Sol No_02%, NN_02$, SINVS_02&
   
Next_No_02:
Next No_02

End Sub


Sub RESUME_LAST_SOLUTION()

TOT_INV = Cells(1, 6)

ReDim RESUME_No(TOT_INV)

LAST_SOLUTION_No = Cells(5, 6)
If LAST_SOLUTION_No = 0 Then Exit Sub
LAST_SOLUTION = Cells(LAST_SOLUTION_No, 8)

Range("D:D").Select
Selection.ClearContents

If LAST_SOLUTION <> "" Then

LAST_SOLUTION = Trim(LAST_SOLUTION) + " "

SOL_LEN = Len(LAST_SOLUTION)
START_LEN = 1

AA = 1
For i = START_LEN To SOL_LEN

No = InStr(i, LAST_SOLUTION, " ")

Cells(AA, 4) = Mid(LAST_SOLUTION, i, No - i)
RESUME_No(AA) = Cells(AA, 4)
i = No
AA = AA + 1

Next

End If


End Sub
Sub COPY_SOLUTIONS()

AC_NAME = ActiveSheet.Name

N = 0
SOL_NAME = Cells(2, 6)

Do
  N = N + 1
  SOL_NAME_01 = Trim(Str(SOL_NAME)) + "_" + Trim(Str(N))
Loop Until Exist_SHEET(SOL_NAME_01) = 0

Cells(7, 6) = N

Create_SOLUTIONS_PAGE (SOL_NAME_01)

Sheets(AC_NAME).Select
Range("H1:I65536").Select
Selection.Copy

Sheets(SOL_NAME_01).Select
Range("B1").Select
ActiveSheet.Paste
Columns("B:C").AutoFit
Range("B1").Select

Sheets(AC_NAME).Select
Range("H1:I65536").Select
Selection.ClearContents
Range("E10").Activate

End Sub
Function Exist_SHEET(SH_NAME)

Exist_SHEET = 0

For Each SH In Sheets
    If SH.Name = SH_NAME Then Exist_SHEET = 1: Exit For
Next SH
 
End Function

Sub Create_SOLUTIONS_PAGE(SH_NAME)

If Exist_SHEET(SH_NAME) Then
   Else
       Set NewSheet = Worksheets.Add
       NewSheet.Name = SH_NAME
End If

End Sub

Sub RESUME_Challenge()

If Cells(5, 6) = 0 Then Exit Sub

' SORTING
Columns("B:B").Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

RESUME_LAST_SOLUTION

RESUME_CALC = 1

Cells(11, 4).Select
Selection.Activate

MAX_RESUME_No = Cells(8, 6)

CHECK = Cells(2, 6) * 100

TOT_INV = Cells(1, 6)
ReDim INV(TOT_INV + 1)

MaxSum = 0
For i = 1 To TOT_INV
    INV(i) = Cells(i, 2) * 100
    MaxSum = MaxSum + INV(i)
Next

SUM_INV = 0

MAX_INVS = 0

For i = 1 To TOT_INV
   SUM_INV = SUM_INV + INV(i)
   If SUM_INV = CHECK Then MAX_INVS = i: Exit For
   If SUM_INV > CHECK Then MAX_INVS = i - 1: Exit For
Next

Cells(3, 6) = MAX_INVS

MAX_CHECK_INVS_No = 1

For i = TOT_INV To 2 Step -1
    SUM_INV = INV(i)
    If SUM_INV = CHECK Then GoTo Exit_for
    For k = 1 To i
        SUM_INV = SUM_INV + INV(k)
        If SUM_INV <= CHECK Then
           GoTo Exit_for
        End If
    Next
Next_i:
Next
Exit_for:
MAX_CHECK_INVS_No = i
INV(MAX_CHECK_INVS_No + 1) = MaxSum

Cells(4, 6) = MAX_CHECK_INVS_No

CHECK = MaxSum * 2
RES_No = 0
Sol = Cells(5, 6)

Cells(18, 6).Value = Cells(19, 6).Value

Cells(14, 6) = Time
No_01% = RESUME_No(1)

'Application.ScreenUpdating = False

    RESUME_Find_Sol No_01%, "", 0

    Cells(10, 5) = Str(MAX_CHECK_INVS_No)
    Cells(15, 6) = Time
    
Application.ScreenUpdating = True

End Sub
    
Sub RESUME_Find_Sol(No_01 As Integer, NN_01 As String, SINVS_01 As Long)
    
For No_02% = No_01 + 1 To MAX_CHECK_INVS_No
    
    If RESUME_CALC = 1 Then
       RES_No = RES_No + 1
       If RES_No > MAX_RESUME_No Then
             RESUME_CALC = 2
             CHECK = Cells(2, 6) * 100
             No_02% = No_02% - 1
             Exit For
         Else
            No_02% = RESUME_No(RES_No)
       End If
    End If
    

    NN_02$ = NN_01 + Str(No_02%)
    SINVS_02& = SINVS_01 + INV(No_02%)
    
    If SINVS_02& > CHECK Then Exit For
    If SINVS_02& = CHECK Then
       Sol = Sol + 1
       If Sol > 65536 Then Sol = 1: COPY_SOLUTIONS
       
       Cells(Sol, 8) = NN_02$
       'Cells(10, 5) = NN_02$
       Cells(15, 6) = Time
                 
    End If
        
    If (SINVS_02& + INV(No_02% + 1)) > CHECK Then
           
       If (INV(No_02%) = INV(No_02% + 1)) Then GoTo END_LOOP
       
       MAX_No_01% = MAX_CHECK_INVS_No + 1
       No_02% = No_02% + 1
       
START_LOOP:
      
       CH_No% = MAX_No_01% - No_02%
       
       If CH_No% > 1 Then
          CH_No_m% = CH_No% / 2 + No_02%
      
          If (SINVS_01 + INV(CH_No_m%)) > CHECK Then
             MAX_No_01% = CH_No_m%
             GoTo START_LOOP
          End If
          If (SINVS_01 + INV(CH_No_m%)) = CHECK Then
             Sol = Sol + 1
             If Sol > 65536 Then Sol = 1: COPY_SOLUTIONS
             Cells(Sol, 8) = NN_01 + Str(CH_No_m%)
             Exit For
          End If
          If (SINVS_01 + INV(CH_No_m%)) < CHECK Then
             No_02% = CH_No_m%
             GoTo START_LOOP
          End If
        Else
          If CH_No% = 1 Then
             No_02% = MAX_No_01% - 1
             If (SINVS_01 + INV(No_02%)) = CHECK Then
                Sol = Sol + 1
                If Sol > 65536 Then Sol = 1: COPY_SOLUTIONS
                   Cells(Sol, 8) = NN_01 + Str(No_02%)
                  Exit For
                End If
             End If
             Exit For
          End If
       End If
END_LOOP:
         
    RESUME_Find_Sol No_02%, NN_02$, SINVS_02&

Next_No_02:
Next No_02

End Sub


.. Module 2 ... 


		Code:
__


Dim Comp_No()
Function Print_No(Combination As String, Base As Integer) As Variant

Application.ScreenUpdating = False

Dim COMP As Integer
Dim Max_COMP As Integer
Dim ss, dd, i, k As Integer
Dim Co_02, Co_01 As Integer

Max_COMP = Val(SEPARATE_COMP(Combination, 0))
ReDim Comp_No(Max_COMP)

Dim Comp_SER(10, 2)

For COMP = 1 To Max_COMP
    Comp_No(COMP) = Val(SEPARATE_COMP(Combination, COMP))
Next

ss = 0: dd = 0

Select Case Max_COMP
 Case 1
      Print_No = CDec(1)
      For COMP = 1 To Comp_No(1) - 1
          Print_No = CDec(Print_No + Count_Combinations(COMP, Base))
      Next
 Case Base: Print_No = CDec(Max_COMP)
 Case Else
      Co_01 = Comp_No(1)
      For i = 2 To Max_COMP
          Co_02 = Comp_No(i)
          If Co_02 - Co_01 = 1 Then
                Co_01 = Co_02
                dd = dd + 1
            Else
                ss = ss + 1
                Comp_SER(ss, 1) = Co_01
                Comp_SER(ss, 2) = Co_02
                Co_01 = Co_02
          End If
      Next
      If ss = 0 Then
            Print_No = CDec(Max_COMP)
            For COMP = 1 To Comp_No(1) - 1
                Print_No = CDec(Print_No + Count_Combinations(COMP, Base))
            Next
        Else
            Print_No = CDec(Print_No + Max_COMP)
            For i = 1 To ss
                For k = Comp_SER(i, 1) + 1 To Comp_SER(i, 2) - 1
                   Print_No = CDec(Print_No + Count_Combinations(k, Base))
                Next
            Next
            For COMP = 1 To Comp_No(1) - 1
                Print_No = CDec(Print_No + Count_Combinations(COMP, Base))
            Next
            
      End If
End Select

Application.ScreenUpdating = True

End Function

Function Count_Combinations(No As Integer, Base As Integer) As Variant

Count_Combinations = CDec(2 ^ (Base - No))

End Function

Function SEPARATE_COMP(CELL_TEXT As String, No As Integer) As String
Application.ScreenUpdating = False

If CELL_TEXT = "" Then SEPARATE_COMP = "": Exit Function

' COUNT WORDS

CELL_TEXT = Trim(CELL_TEXT) + " "
TEXT_LEN% = Len(CELL_TEXT)
START_LEN% = 1
COUNTER_No% = 1
For i% = START_LEN% To TEXT_LEN%
    FOUNT_POSITION_No% = InStr(i%, CELL_TEXT, " ")
    WORD_FOUND = Mid(CELL_TEXT, i%, FOUNT_POSITION_No% - i%)
    i% = FOUNT_POSITION_No%
    If Trim(WORD_FOUND) <> "" Then
       COUNTER_No% = COUNTER_No% + 1
    End If
Next

MAX_WORDS% = COUNTER_No% - 1

If No = 0 Then
   SEPARATE_COMP = MAX_WORDS%
   Application.ScreenUpdating = True
   Exit Function
End If

' PUT WORDS IN ARRAY
ReDim WORDS_FOUND(MAX_WORDS%)

START_LEN% = 1
COUNTER_No% = 1

For i% = START_LEN% To TEXT_LEN%
   FOUNT_POSITION_No% = InStr(i%, CELL_TEXT, " ")
   WORD_FOUND = Mid(CELL_TEXT, i%, FOUNT_POSITION_No% - i%)
   i% = FOUNT_POSITION_No%
   If Trim(WORD_FOUND) <> "" Then
      WORDS_FOUND(COUNTER_No%) = WORD_FOUND
      COUNTER_No% = COUNTER_No% + 1
   End If
Next

If No > MAX_WORDS% Then No = MAX_WORDS%
SEPARATE_COMP = WORDS_FOUND(No)
    
Application.ScreenUpdating = True

End Function


.. Create two Modules and copy the code above to them ..


----------



## IOANNIS

BRUTE_FORCE_FINAL.xlsABCDEFGH1177,740RES_01TOTALINVOCIES54123456789101112131415182034362283,060RES_02CHECK4556,92123456789101112131416192328383389,400RES_03MAX_SUM_NUMBERS211234567891011121315161718244544116,140RES_04MAX_CHECK_No5312345678910111213161728334055126,690RES_05CURRENTSHEET_SOLUTIONS8412345678910111213162226274466144,770RES_06TOTAL_SOLUTIONS_FOUND84123456789101112131728364977160,620RES_07TOTALSHEETSOLUTIONS0123456789101112132531364188185,580RES_08MAX_RESUME_No012345678910111214152224274699192,650RES_09LASTSOLUTIONFOUNDorCOMBINATION1234567891011121416172133461010194,580RES_105312345678910111214202841441111219,100RES_11CHECKEDCOMBINATIONS=Print_No(E10;F4)123456789101112151620222427281212222,520RES_12TOTAL_COMBINATIONS9.007.199.254.740.990123456789101112151719202124361313228,310RES_13COMBINATION'S%DONE=(F11/F12)*10012345678910111217183437381414230,720RES_14MACRO'STIMESTART11:25:3212345678910111217212332491515244,220RES_15LASTFOUNDSOLUTION'STIME11:25:3512345678910111224262730421616280,710RES_16ELAPSEDTIME0:00:031234567891011131416181931481717324,840RES_17REMAINTIME=F19*((100-F13)/F13)1234567891011131418232830351818329,170RES_18LASTRESUMETIME0:00:0012345678910111314242633481919329,970RES_19TOTALCALCTIME0:00:0312345678910111315163738422020346,350RES_20SOLUTION'SSPEED/HOUR100.80012345678910111315182642462121365,430RES_2112345678910111318243135372222409,170RES_2212345678910111320212932432323440,930RES_2312345678910111415171946472424441,430RES_24123456789101114151720212226312525444,980RES_251234567891011141518212526452626456,680RES_261234567891011141617293546August 2002 Challenge


----------



## IOANNIS

.. Create a sheet similar to the above screen shot..

..Copy formulas to the same cells ..

..Format the time's cells as time "h:mm:ss"

..Create two Buttons
..One with a Name "Find New" and assign the macro "Challenge"
..and the second one with a Name "Resume" with the macro "RESUME_Challenge"

The macro uses the "numbers" and the "check" on the sheet (you can replace the "numbers" or the "check" as you wish")

The resume button works only if there is one solution found and the state off VBA is not debug

The resume macro is a little bit slower

If "Application.ScreenUpdating = False" you cant see anything unless you break the code with [ctrl]+[break]

The Macro uses two filtters ..

1: If SINVS_02& > CHECK Then Exit For
2: If (SINVS_02& + INV(No_02% + 1)) > CHECK Then
  ..at this point the macro use the dividing method (i think this is the name)


If  you Want more infos just let me know ..



_________________
ATHENS 2004
This message was edited by  IOANNIS on 2002-09-03 01:43 
This message was edited by  IOANNIS on 2002-09-03 08:44


----------



## IOANNIS

............
If you Enable the formulas in cells 
  1:f11
  2:f13
  3:f17

you have to un remark the line in the code below
       Cells(Sol, 8) = NN_02$
   =>  'Cells(10, 5) = NN_02$ 
       Cells(15, 6) = Time

 ...........

_________________
ATHENS 2004
This message was edited by  IOANNIS on 2002-09-03 01:58


----------



## MrExcel

What an excellent discussion!  I would guess my vote is to award IOANNIS and Sharad Kothari as co-winners for the August challenge and to send a message board CD to them both.   

There were some private entries that I need to review and I will post anything new from those entries here in the next day.

While I do that, does anyone have an idea for a September Challenge of the Month?

Bill


----------



## IOANNIS

An idea for a September Challenge of the Month

When the Euro become the currency of most European Countries (as my, Greece), one problem occurs when my company wants to pay  parttime Employees in cash two times a month.

The salaries was exact amounts, 150000 grd 140000 grd etc ..

150000= 15 x 10000 (banknote of 10000 grd)
140000= 14 x 10000 (banknote of 10000 grd)

But in Euro not 

150000 grd=440,21 Euro
140000 grd=410,86 Euro

440,21 = 8x50 + 2x20 +1x0,20 + 1x0,01
410,86 = 8x50 + 1x10 +1x1 +1x0,50 + 1x0,20 + 1x0,10 +1x0,05 + 1x0,01

(if we convert them to dollars the problem is the some)

The Company wants to enclose to an envelope the salary with the minimum banknotes and coins  

Euro banknotes 

1x500 euro
1x200 euro
1x100 euro
1x500 euro
1x20 euro
1x10 euro
1x5 euro

Coins

1x5 euro
1x2 euro
1x1 euro
1x0,50 cents
1x0,20 cents
1x0,10 cents
1x0,05 cents
1x0,02 cents
1x0,01 cents

The problems are 3 :

1:If we don't have any banknotes and coins, how many banknotes and coins must get it from the Bank to make the pays ?

2:if we have some of them how many envelopes we can make ?

3:If we want not to pay with banknotes of 500 or (and) 200 etc ? or the BANK do not have any banknotes of 500 or (and) 200 etc ?

The salaries are change every 15 days since they are parttime

We want to make a macro too split the salaries to minimum banknotes and coins and solve the three problems above .(Use random salaries, use local currency)



(sorry if my english is not so good)


----------



## Juan Pablo González

I used a Solver model for this:Libro1ABCDEFGHIJKLMNOPQRSTU1Salary50020010050201055210.50.20.10.050.020.01DifferenceTotalbanknotesTotalcoinsTotal2440.21020020000001000104263410.8602000100001111100358Hoja1

I solved each row at a time, but, that can be changed.  Solver model:

Target cell: U2
Minimum cell value

Changing cells: B2:Q2
Restrictions

B2:Q2 = Integer
R2 = 0

And in "Options", I checked "Assume non negatives".  Using more restrictions you can answer all your questions...


----------



## IOANNIS

Thanks Juan 
I have also solved it without solver

Do you intersting to show you a screen shot ?



Regards


----------



## Juan Pablo González

Sure ! learning is always welcome...


----------



## IOANNIS

A JavaScript error occur when i try to post the screen shot in testing forum, too much code i think ...


----------



## IOANNIS

I change a little bit my macro and the speed now is 69.443 per hour, 10 hours & 43 minites

As i said before the macro does not use any Excel's features and is easy to tranfered to another programming language sush as c++ for optimum speed.
Since I have not a c++ compiler but an old fashion Quick basic Advanced Compiler Version 7.00 (edition 1989 !!!), i copy-paste the code, make the proper changes and after a few minutes the brute.exe was ready to run.

THE RESULTS:
Total time 3 hours & 39 minites
Final speed 204.000 per hour

If an old basic compiler (1989)is so fast what about an c++ (2002) ?

(Remarks for WIN XP users for speeding up their computer:
..go to System Properties =>Advanced => Performance Settings and check the "adjust for best performance", thats was when i said that "something has my computer at home", it speed ups my macro up to 50%)


----------



## DUTCHMAN

*Help*

Hi ,

I'm working in the bookkeeping and i have this problem to solve every day.

Can some body explaine how to put this macro in an excel file, or send me a excel ark with the macro in place.


----------



## IOANNIS

..Comments ........

The macro on the http://www.mrexcel.com/pc09.shtml dont work without the original worksheet ......

If someonne wants to run the macro just email me to sent him the original workbook 

.......


----------



## opeyemi1

> ..Comments ........
> 
> The macro on the http://www.mrexcel.com/pc09.shtml  dont work without the original worksheet ......
> 
> If someonne wants to run the macro just email me to sent him the original workbook
> 
> .......



Helloooo,

Could you please provide me with workbook you mentioned so I can see if the macro will work for me, my email is:  opeyemi1*removethispart*@hotmail.com

Also, wondering if you have any inputs in the following:


I have 2 spreadsheets, and they are not linked together by formulas. One of them has various lines (an income statement) that sum different lines from the other spreadsheet, e.g.

Sales = 200100
Expenses = -300345
Other Income = 120000
Selling and Admin = 35000

The other spreadsheet has a lot of details (a trial balance) that make up this first spreadsheet.

e.g.

Paper 100000
Cardboard 50100
bottles 30000
cups 20000

These add up to the sales number for example.

Is there a way that I can use the macro or something similar to get all the cells that relate to the first spreadsheet in one shot, and so I don't have to first find what the sales number is made up of, and then what the expenses number is made up of..can I look for the relating cells to all the items in the 1st spreadsheet in one go. Is there a code or something that I can use.

The 2nd spreadsheet has a lot of rows, and it's quite difficult to manually analyze.  I tried using the solver in excel, but I find that when I look for the sum of the sales number and try looking for the expenses number, it gives me cells that are related to the sales number as well.

Thanks for your help!!!


----------



## IOANNIS

^ I email the workbook to opeyemi1@hotmail.com ...


----------



## IOANNIS

*Re: Help*



> Hi ,
> 
> I'm working in the bookkeeping and i have this problem to solve every day.
> 
> Can some body explaine how to put this macro in an excel file, or send me a excel ark with the macro in place.



If you want the macro-workbook just pm me


----------



## tsdiesel

I PM'd IOANNIS a few weeks back and still have not heard from him.  However I would really like to be able to put this macro to work.  Would anyone mind PMing me with a link to download this workbook or perhaps we can exchange emails?

Thank you!
TS


----------



## PaddyD

Don't know about Ioannis, but Tushar's file here is built with a similar intent:

http://www.tushar-mehta.com/excel/templates/match_values/index.html


----------



## tsdiesel

Thanks so much!


----------



## texastomass

Can anyone supply me with a walkthrough for Ioannis solution. This could really help in work but am only just beginning to ctreate an understanding of VB.


Thanks

Edited by Moderator to remove email address. Refer point 19 of the Posting Rules


----------



## kuthbudeen

Can anyone supply me with a walkthrough for Ioannis solution. This could really help in work.

Edited by Moderator to remove email address. Refer point 19 of the Posting Rules


----------



## Biz

Juan Pablo González said:


> I used a Solver model for this:
> 
> 
> ******** ******************** ************************************************************************><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" bgColor=#0c266b colSpan=22><TABLE border=0 width="100%" align=center><TBODY><TR><TD align=left>Microsoft Excel - Libro1</TD><TD style="FONT-FAMILY: caption; COLOR: #ffffff; FONT-SIZE: 9pt" align=right>___Running: xl2000 : OS = Windows NT 4 </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px; BORDER-RIGHT: #000000 0.5pt solid" bgColor=#d4d0c8 colSpan=22><TABLE border=0 width="100%" align=center VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-FAMILY: caption; COLOR: #000000; FONT-SIZE: 10pt">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT value="Copy Formula" type=button name=btCb942116 *******='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);'></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" bgColor=white colSpan=22><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" bgColor=white align=middle><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION selected value==A2-SUMPRODUCT($B$1:$Q$1,B2:Q2)>R2<OPTION value==SUM(B2:H2)>S2<OPTION value==SUM(I2:Q2)>T2<OPTION value==S2+T2>U2<OPTION value==A3-SUMPRODUCT($B$1:$Q$1,B3:Q3)>R3<OPTION value==SUM(B3:H3)>S3<OPTION value==SUM(I3:Q3)>T3<OPTION value==S3+T3>U3</OPTION></SELECT></TD><TD bgColor=#d4d0c8 width="3%" align=right>*=*</TD><TD bgColor=white align=left><INPUT value==A2-SUMPRODUCT($B$1:$Q$1,B2:Q2) size=80 name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" width="2%" align=middle>
> 
> </TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>A</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>B</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>C</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>D</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>E</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>F</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>G</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>H</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>I</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>J</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>K</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>L</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>M</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>N</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>O</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>P</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>Q</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>R</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>S</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>T</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=middle><CENTER>U</CENTER></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>1</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Salary</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">500</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">200</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">100</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">50</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">20</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">10</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">5</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">5</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">0.5</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">0.2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">0.1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">0.05</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">0.02</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">0.01</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Difference</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Total*banknotes</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Total*coins</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 0.5pt solid">Total</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>2</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">440.21</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">4</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">6</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=middle><CENTER>3</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">410.86</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">0</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">3</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">5</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">8</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan=22><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; WIDTH: 120pt; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=left>Hoja1</TD><TD>*</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
> [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
> PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
> 
> I solved each row at a time, but, that can be changed. Solver model:
> 
> Target cell: U2
> Minimum cell value
> 
> Changing cells: B2:Q2
> Restrictions
> 
> B2:Q2 = Integer
> R2 = 0
> 
> And in "Options", I checked "Assume non negatives". Using more restrictions you can answer all your questions...


 

Hi , 

I tried solver but I don't get the same answer as you. Is there any additional criteria’s I need to add?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<TABLE style="WIDTH: 503pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=667 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" span=3 width=28><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" span=3 width=21><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" span=4 width=14><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" span=3 width=25><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" span=2 width=32><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl24 height=35 width=64>*Salary*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 21pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" id=td_post_95016 class=xl29 width=28 x:num>*500*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 21pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=28 x:num>*200*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 21pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=28 x:num>*100*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 16pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=21 x:num>*50*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 16pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=21 x:num>*20*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 16pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=21 x:num>*10*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 11pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=14 x:num>*5*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 11pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=14 x:num>*5*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 11pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=14 x:num>*2*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 11pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=14 x:num>*1*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 19pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=25 x:num="0.5">*1*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 19pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=25 x:num="0.2">*0*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 19pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=25 x:num="0.1">*0*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 26pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=35 x:num>*0.05*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 24pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl30 width=32 x:num="0.02">*0*</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ccffcc; WIDTH: 24pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl31 width=32 x:num="0.01">*0*</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #ff99cc; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl25 width=64>*Difference*</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #99ccff; WIDTH: 45pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl26 width=60>*Total*banknotes*</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #99ccff; WIDTH: 47pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl26 width=63>*Total*coins*</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: #99ccff; WIDTH: 29pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl27 width=39>*Total*</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl22 height=17 width=64 x:num>440.21</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=28 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=28 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=28 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 16pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=21 x:num>7</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 16pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=21 x:num>3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 16pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=21 x:num>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 19pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=25 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 19pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=25 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 19pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=25 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 26pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=35 x:num>3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 24pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=32 x:num>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 24pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=32 x:num>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=64 x:num x:fmla="=A2-SUMPRODUCT($B$1:$Q$1,B2:Q2)">0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 45pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=60 x:num>4</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=63 x:num>2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 29pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #666699 1pt solid" class=xl28 width=39 x:num x:fmla="=S2+T2">6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl22 height=17 width=64 x:num>410.86</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=28 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=28 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=28 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 16pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=21 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 16pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=21 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 16pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=21 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 11pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=14 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 19pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=25 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 19pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=25 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 19pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=25 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 26pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=35 x:num>8217</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 24pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=32 x:num>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 24pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=32 x:num>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=64 x:num x:fmla="=A3-SUMPRODUCT($B$1:$Q$1,B3:Q3)">0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 45pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=60 x:num>3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: silver 0.5pt solid" class=xl23 width=63 x:num>5</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: white; WIDTH: 29pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #666699 1pt solid" class=xl28 width=39 x:num x:fmla="=S3+T3">8</TD></TR></TBODY></TABLE> 
Biz


----------



## johndrew

What cell address do I put my total invoice payment received?
What column do I put my details of individual amounts due?

Thanks for your usual support


----------



## bromy2004

I've noticed a few people were looking for the workbook from IOANNIS.
Here is a walkthrough.

1. Create a New Sheet
2. In Column B paste the Values (Your own or from the challenge)
3. Column A have an incrementing count (1,2,3,4 etc)
4. Leave C/D blank
5. the Range E1 to F20 should look like this

Excel WorkbookEF1Total Invoices542CHECK* * * * * * * * * * * * * 4,556.923MAX_SUM_NUMBERS214MAX_CHECK_No535CURRENT SHEET_SOLUTIONS06TOTAL_SOLUTIONS_FOUND07TOTAL SHEET SOLUTIONS08MAX_RESUME_No09LAST SOLUTION FOUND or *COMBINATION100*11CHECKED COMBINATIONS112TOTAL_COMBINATIONS9.0072E+1513COMBINATION'S % DONE1.11022E-1414MACRO'S TIME START*15LAST FOUND SOLUTION'S TIME*16ELAPSED TIME12:00:00 AM17REMAIN TIME12:00:00 AM18LAST RESUME TIME12:00:00 AM19TOTAL *CALC TIME12:00:00 AM20SOLUTION'S SPEED/HOUR#DIV/0!Sheet1
6. Add 2 Modules (Module1 and Module2)
7. Copy the appropriate code to the appropriate module
8. IMPORTANT: Make sure you don't have Option Explicit in either module.
9. Run the macro "Challenge"


----------



## Googamanga

This is awesome!

The macro wrorks like a dream!


----------



## johndrew

Which Program was that?


----------



## Googamanga

...  IOANNIS

http://www.mrexcel.com/pc09.shtml


----------



## Fortress58

Can this macro be modified to find combinations of x numbers?

Is it possible to modify it just for combinations of 6 numbers or 12 numbers or any specified amount of numbers that match certain value and discard the others?

That would help the person who wants to apply the check only to a specified number of invoices or anyone like me for lotto analysis.

I am not a macro person but I know many of you are great and like a challenge.

I have tried it for lotto analysis but most combinations are of more than 12 numbers which are unusable for analysis.
Please give me a hand with my lottery .


----------



## IOANNIS

Original Excel WorkSheet, Download link

http://www.humyo.com/DZrSCzy/FILES/BF_FINAL_NEW_05.XLS?a=Z3Y3HJRg9Vo


----------



## starl

Permanent attachment of Ioannis' solution


----------



## IOANNIS

I receive PMs for the error mentioned from LxQ in the topic below

http://www.mrexcel.com/forum/showthread.php?p=3028754#post3028754

in the macro in the “Accounts Receivable Challenge from Aug 2002”.

I correct the macro it is ok now

Download link > https://dc2.safesync.com/DZrSCzy/FIL...?a=ruRPyGA0xL4

Save the file and rename it to BF_FINAL_XL2010.xlsm and then open it with Excel 2010


----------



## luckydog44

Is there a set of instructions on how to properly work this utility? I have toyed with it a bit but it always seems to just do what it wants to.


----------



## silviu

All the links appear not to be working. Could we please have the macro attached again?

Thank you!


----------



## IOANNIS

silviu said:


> All the links appear not to be working. Could we please have the macro attached again?
> 
> Thank you!



https://skydrive.live.com/redir?resid=72D3AB6827361FC6!111&authkey=!AEvJ2Dkn5-tgUQE


----------

