# Filtering Records Based On A Criteria Value

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

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

 Filter Range Criteria Output 1394 3232.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

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

@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 Range Criteria Output CellReference 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

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

Is there anyone in the community who can assist me to figure out the problem.

# Re: Filtering Records Based On A Criteria Value

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"
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``````

# Re: Filtering Records Based On A Criteria Value

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

Thanks again.

# Re: Filtering Records Based On A Criteria Value

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

Can you please check attached file.

Regards

# Re: Filtering Records Based On A Criteria Value

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 ？

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

@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.

# Re: Filtering Records Based On A Criteria Value

See the attached version.

# Re: Filtering Records Based On A Criteria Value

Acknowledge with thanks.

# Re: Filtering Records Based On A Criteria Value

@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.

# Re: Filtering Records Based On A Criteria Value

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.

# Re: Filtering Records Based On A Criteria Value

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

# Re: Filtering Records Based On A Criteria Value

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.

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.

Click OK.

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

The Solver Parameters dialog should now look like this:

Click Solve.

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

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

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.