Forum Discussion
Filtering Records Based On A Criteria Value
My requirement is to filter records based on a criteria value. List of records in Column A (Filter range) Criteria in Column B Output in Column C Criteria is sum of three different ranges i...
Is there anyone in the community who can assist me to figure out the problem.
In Excel, select File > Options > Add-ins, then click Go...
Tick the check box for Solver, then click OK.
In the Visual Basic Editor, select Tools > References...
Tick the check box for Solver, then click OK.
Macro:
Sub FindSolution()
SolverReset
SolverOk SetCell:="$D$2", MaxMinVal:=3, ValueOf:=Range("C2").Value, _
ByChange:="$B$2:$B$22", Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:="$B$2:$B$22", Relation:=5
SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.00001, Convergence:=0.1, _
StepThru:=False, Scaling:=False, AssumeNonNeg:=True, Derivatives:=2
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, _
Multistart:=False, RequireBounds:=False, MaxSubproblems:=0, _
MaxIntegerSols:=0, SolveWithout:=False, MaxTimeNoImp:=30
SolverSolve UserFinish:=True
End SubI have added solver and run the macro but its not working.
Can you please check attached file.
Regards
Amjad
Background of the problem:
We have received payment form a customer who has many bills outstanding, may be hundreds of bills are outstanding. Upon receipt of payment, we need to identify bills those make sum of this payment which we received.
An altogether different solution, coming at it from a different direction.
- Do you charge interest on unpaid balances beyond a certain time limit? (Doing so would alter the payable amount of original invoices.)
- Is there any reason why you can't just apply any given payment to the oldest of the outstanding bills, then the next oldest, in sequence, until the point where a given outstanding bill is not fully covered. At that point, that last of the series gets partially paid, but now becomes the oldest, to be paid off in the same manner with whatever payment comes in next.
- In other words, what makes it so important that you seek out the exact combination that equals the payment? And what do you do if there are multiple combinations, which is certainly possible if there are hundreds of such outstanding bills?
- And if there IS a reason to seek out the exact combination, why not require the customer to identify the bills being paid? Presumably the customer knows, since you are assuming that the customer is paying a specific combination.
- To have the customer identify them seems entirely reasonable, especially given the possibility (probability) that you might (mathematically) identify one or two in error, given the volume of outstanding bills.
