Forum Discussion
amjadinsaudia
Oct 10, 2023Copper Contributor
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...
amjadinsaudia
Oct 11, 2023Copper Contributor
any three contiguous cells ranges in Column A where the sum equals the stated criterion
Just the series of values meeting the criterion.
Result to be in Column C
A more illustrated table is pasted here.
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.
Filter Range | Criteria | Output | Cell Reference | Cell Total | |
1394 | 3232.83 | ||||
1386.64 | |||||
532 | |||||
26 | |||||
486 | A6:A9 | 2168.66 | |||
220.66 | A15:A19 | 829.39 | |||
1062 | A22 | 234.78 | |||
400 | 3232.83 | ||||
126.27 | |||||
170 | |||||
3810 | |||||
700 | |||||
500 | |||||
22 | |||||
120 | |||||
230 | |||||
35.65 | |||||
421.74 | |||||
2129.56 | |||||
241.5 | |||||
234.78 |
mathetes
Oct 11, 2023Silver Contributor
You seem to have agreed with the three contiguous cells as part of the criterion, so here's a sample of how that can be done, with a cell off to the right that gives a margin of error, since it's not always going to be the case that you get exactly that criterion amount.
But this only looks at the sum of each set of three contiguous cells. Those combinations that come within the percentage margin of error are highlighted in green.
- amjadinsaudiaOct 12, 2023Copper ContributorNo, I am not agreed that always three contiguous cells will be part of criteria.
Any combination of cells either contiguous or separate or starting cells or ending cells, could make a total which will be equal to criteria.
Any further assistance will be much appreciated.
This workbook is not fulfilling the requirement and there will be a need of a VBA code.
Thanks.- amjadinsaudiaOct 15, 2023Copper ContributorIs there anyone in the community who can assist me to figure out the problem.
- HansVogelaarOct 15, 2023MVP
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 Sub