Filtering Records Based On A Criteria Value

Copper Contributor

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 in Column A

 

A VBA code is required  provide output in Column C that these values are equal or sum of criteria.

 

Filter RangeCriteriaOutput
13943232.83 
1386.64  
532  
26  
486  
220.66  
1062  
400  
126.27  
170  
3810  
700  
500  
22  
120  
230  
35.65  
421.74  
2129.56  
241.5  
234.78  
25 Replies

@amjadinsaudia 

 

I think you need to add some clarity to your inquiry.

 

When you say "Criteria is sum of three different ranges in Column A"  do you mean you want to find

  • any three cells in Column A where the sum equals the stated criterion, or
  • any three cells in Column A where the sum is less than or equal to the stated criterion, or
  • any three contiguous cells in Column A where the sum equals the stated criterion, or
  • any three contiguous cells in Column A where the sum is less than or equal to the stated criterion
  • or every combination of cells, be they contiguous or not...
  • etc

 

What is the result supposed to look like? Identification of the cells in question? Just a series of totals meeting the criterion? Does the result have to be in column C?

 

In short, your question raises more questions that need to be answered. Once you've answered them, you might have solved your own problem.

 

@mathetes

 

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 RangeCriteriaOutput 

Cell

Reference

Cell Total
13943232.83    
1386.64     
532     
26     
486   A6:A92168.66
220.66   A15:A19829.39
1062   A22234.78
400    3232.83
126.27     
170     
3810     
700     
500     
22     
120     
230     
35.65     
421.74     
2129.56     
241.5     
234.78     

 

@amjadinsaudia 

 

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.

 

mathetes_0-1697052280287.png

 

No, 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.
Is there anyone in the community who can assist me to figure out the problem.

@amjadinsaudia 

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

@Hans Vogelaar 

 

Thank you. Once I will do it and will let u know.

 

Thanks again.

@Hans Vogelaar 

I have added solver and run the macro but its not working.

Can you please check attached file.

Regards

Amjad

3232.83:
22,35.65,120,220.66,230,234.78,400,421.74,486,1062
26,35.65,170,220.66,230,234.78,421.74,500,1394
26,35.65,220.66,234.78,400,421.74,500,1394
26,35.65,220.66,234.78,421.74,532,700,1062

sum ?

@amjadinsaudia 

I'm sorry, I forgot to mention that I had inserted a column, but the macro doesn't work. It fails to add the restriction.

I'm afraid I don't know how to do this.

@amjadinsaudia 

 

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.
We do not charge any interest on unpaid balances.
Sure, we can ask the supplier to give us breakup of the amount we received from him and certainly he knows how the amount is make up. We will ask the supplier.
Thanks.
@Hans Vogelaar
Can you please share with me the Excel file which has this macro and results are being generated in rows instead of columns?
Thanks.

@amjadinsaudia 

See the attached version.

@Hans Vogelaar 

 

Acknowledge with thanks.

@Hans Vogelaar

Is this file to be open in excel online as the function filter is part of online version only and then how to run macro in it?

 

Or if this to be open in offline 2016 then I am facing with an name error in column E.

 

Awaiting for a reply please!

@amjadinsaudia 

Macros don't work in Excel Online.

The macro runs in the desktop version of Excel, but as I told you before, it doesn't work correctly. SolverAdd doesn't add a constraint. I don't know how to solve this.

Running Solver manually does work.

How to run solver manually? any guidance will be appreciated.

@amjadinsaudia 

Open the workbook that I attached to my previous reply.

Select cell D2.

On the Data tab of the ribbon, click Solver.

Select the 'Value of' option button, and enter 3232,83 in the box next to it.

Click in the 'By Changing Variable Cells' box, then point to B2:B22.

Click the Add button.

In the 'Add Constraint' dialog, click in the 'Cell Reference' box, then point to B2:B22

Select bin from the drop-down next to it.

HansVogelaar_0-1697632242973.png

Click OK.

In the 'Select a Solving Method' drop-down, select 'Simplex LP'.

The Solver Parameters dialog should now look like this:

HansVogelaar_1-1697632346088.png

Click Solve.

After a while, the 'Solver Results' dialog should appear.

Make sure that 'Keep Solver Solution' is selected, then click OK.

HansVogelaar_2-1697632467622.png

In column B, a 1 indicates that the corresponding number in column A is included in the sum.

Column E displays the values that are included.

HansVogelaar_3-1697632552102.png