Filtering Records Based On A Criteria Value

HansVogelaar

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.

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.

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.

HansVogelaar 

Thanks for detailed reply and I solved it.

I did and accomplish it with a name error.

I am using office 2016 and might be filter function is not available this version.

However I am able to corresponding value in A with the help of 1 in column B.

Any comments?

HansVogelaar 

Now I added the actual data which goes to the ending range of line no. 231.

In this scenario Excel is replying "Too many variable cells"

File is attached for quick reference please.

Awaiting for a reply.

amjadinsaudia 

According to Define and solve a problem by using Solver :

"You can specify up to 200 variable cells."

So your problem is too complicated for Solver.

HansVogelaar 

noted the message and ack with thanks.

HansVogelaar 

mathetes 

I have achieved my goal 100% with the help Mr. Hans. I would like to thanks to him and Mr. Mathetes for the kind support on this tech forum.

With my regards,

Amjad

amjadinsaudia 

You're welcome!