Macro to extract values based on Month and year

[Howardc1001]

I have dates in Column b and values in Col C


I need to extract the items in Col A to C in sheet 'Data" based in the month and year in Col P and total value in Col Q and copy these to sheet Extraction


I have manually extracted the data


I would like a macro to extract this automatically


Your assistance in resolving this is most appreciated

I have also posted on https://www.mrexcel.com/board/thread...-year.1125517/

[zzzeddy]

Hi Howard

Hope you aren't having any car-owner virus issues.

I have attached my solution for you.

I have named the cell for your specified date as [dateCell]
I have named the cell for your specified Value as [targetAmount]

When you click the shape, a message box will be displayed.
It will tell you how many ways there are of grouping the stock records that match your specified date.

It then uses Solver in VBA to check whether any subset of those stock records can be grouped so that their total value matches your specified Amount.

If there is NO way of grouping any of the stock records (for your chosen date) to match your specified Value, then you will be shown all the stock records that match your specified date.

If you wanted to expand this so that you specified a From-date and a To-date range (rather than just a single date), then we would have to convert your column B dates from text-values to recognised date-values.

zeddy

NOTE:
You need to have the Excel Solver Add-In for this solution to work.
This is found in the Analysis section in the top-panel Ribbon.
If Solver is not there:
Load the Solver Add-in in Excel:
1. In Excel 2010 and later, go to File > Options
2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK. ...
5. After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.

You will also need to add the Solver in the VBA References:
Press Alt-F11 to go to the VBA Editor.
In the top-panel command bar, select Tools>References
..then scroll to find Solver and make sure the checkbox is ticked to make it available to VBA

[Alf]

Nice solution.

Lookin at your solver macro it's possible to simplify it a bit i.e. you have three constraints

Please Login or Register  to view this content.

but you can get the same effect by just using the binary constraint

Please Login or Register  to view this content.

and you can trim this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf

[Howardc1001]

Hi Zeddy

No car-virus issues thank goodness. Hope you have had a good holiday

Lets all hope & pray that this virus will be contained soon

We are in lockdown in SA. We have to stay indoors for 3 weeks.

Thanks for the help with the code and your input, which is highly appreciated


Kind Regards


Howard

[Howardc1001]

Hi Alf

Thanks for your input and streamlined code which I have incorporated into Zeddy's code


Regards


Howard

[Alf]

Hi Howard

You are welcome and thanks for feedback and rep

Alf

[Howardc1001]

Hi Zeddy

Attached please find sample data with the actual number of Columns (sample was only 3 columns)

The spec is exactly the same as my post #1

I am unsure how to amend your code

Kindly make the necessary changes


Regards


Howard

[Howardc1001]

Hi Zeddy

Happy Easter to you & your family


Attached please find my attempt. The spec is examply the same as in Post #1 , except that I have 7 columns. The dates are in Col F & the amount in Col G


Kindly make the changes when you have a chance

Many Thanks


Regards


Howard

[sintek]

VBA Solution if no more than 3 dates...

Please Login or Register  to view this content.

[Howardc1001]

This is great Sintek. Much quicker than using solver

[sintek]

Just remember...Array filter only for 3 values...(dates)

[Howardc1001]

Thanks for the reminder. I will only need a range of two dates for e.g. from 08/01/2020 to say 09/01/2020

[zzzeddy]

Hi Howard

Sintek gives a quicker result than Solver because it doesn't answer the question you originally posed.

To be clear, your first post shows you manually extracted a number of Invoices which had a particular Invoice date.
There were other Invoices with this same date which were excluded from your extracted set.
You were trying to find which set of Invoices, for a specified date, added-up to a specified value.

This is not an uncommon task for someone in accounts, trying to match a payment amount which covers a number of invoices, but you need to find out which ones it covers from a big stack of invoices.

This is really a 2-step process.
First step is to identify which batch of invoices you need to look at.
So you can use a filter-extract method fetch the initial records you are going to focus on, e.g. those with a particular invoice-date.
Then you need to work out which of these will add-up to the payment-amount.
You have to either include them or exclude them from your new pile.

You could eliminate any single-invoice that is larger than that payment value etc etc etc.
But essentially, this could take you a long time.
For example, if you had a pile of 20 invoices, there would be over a million ways to check which ones might add-up to your cheque amount. Good luck with that.
..but this is what Excel's Solver does for you.

I have updated your sample file from post#1, to include more data columns in your source records as requested.
I have added comments to the vba code to help you see what is going on, so you could adapt it if your layout changes.

Please let me know if this does what you need.

My 2 vba routines are named
Sub extractByDate()
and
Sub findRecordsThatAddUpToMyTotal()

..I chose these names to reflect what they are doing!

zeddy

[zzzeddy]

Hi Alf

..re:post#3 apologies for delay

I have included your simplifications in my updated file for Howard.
It is always good to simplify code!
Many thanks for that.

zeddy

[Alf]

Hi zeddy

Thanks for feedback and rep.

By the way did you get this message when you run the macro?
asol_mes.jpg
Don't know if it is some setting on my PC that causes it but clicking "OK" makes the macro run right producing the proper dates and total sum.

I then tested to move the line "SolverReset" and placed it just after the line "SolverSolve True" and that got rid of this pupop message.

As the problem is a linear one (i.e. Simplex LP) and solver selects GRG Nonlinear by default I changed Engine:=1 to Engine:=2 and found my PC got the solution a bit faster using Engine=:2

Alf

[Howardc1001]

Hi Zeddy

Sintek's solution is great and extremely fast

Thanks for updated solution using Solver. I have tested you solution and it works. Also amended code as per Alf's suggestion and it is slightly faster


Regards


Howard

[zzzeddy]

Hi Alf

Re:post#15:
Yes, I did see that message a couple of times, and clicking the [OK] did indeed allow the macro to continue.

At first I thought it was because VBA was 'racing ahead' to finish processing the code, and Excel hadn't kept up, so I added a few..
DoEvents
..to make sure Excel had done all its jobs before continuing with the vba code.
But this didn't seem to fix the issue.

I believe the issue arises because Solver is a bit flaky when it tries to clean-up after finishing if you are using Solver via vba.
I tested your 'move the line "SolverReset" but I still occasionally saw that message.

Using Engine 2 did seem a little faster, but the results were the same.
As far as I remember, Engine 1 is the Linear one, and Engine 2 is the GRG Nonlinear one, not the other way around.

Did you notice that when using Engine 2, Solver sometimes leaves the calc mode in manual when it finished?
This could cause unintentional issues if that file got saved with calcs still set to manual.
Excel's calc mode is determined by the first file opened in an Excel session.
If that first opened file has calc mode set to manual, all subsequent .xlsx files opened will have calc mode switched to manual.
Saving an xlsx file also saves the current calc mode with that file.
You get a ripple effect which can be very annoying to fix.

I shall add a line..
Application.Calculation = xlCalculationAutomatic
..to make sure calcs are turned back ON after Solver has finished.

zeddy

[zzzeddy]

Hi Howard

Sintek's solution is great and extremely fast

As far as I can tell from looking at the code, Sintek's solution just extracts 'visible' records which match an array of dates.
Its a neat way to do that using an array for the required dates.
But it doesn't try to find which of those records Adds up to a required total.
That would require Solver.

Of course, if you just wanted the simple case of extracting records for particular dates, then you don't need to specify a target amount.

zeddy

[Howardc1001]

Thanks for the feedback Zeddy

I do need to specify a target amount as well

[zzzeddy]

Hi Howard

Thanks for clarifying.
I was hoping you would find my comments to be insightful and not inciteful.

zeddy

[Howardc1001]

Haha Zeddy. Your comments are always insightful and never inciteful

[Alf]

Hi zeddy

If you change the macro in your last uploaded file to this

Please Login or Register  to view this content.

and run the macro and then check the solver setting you will see that "Engine:=1" makes solver use the "GRG Nonlinear" engine when it runs.

Did you notice that when using Engine 2, Solver sometimes leaves the calc mode in manual when it finished?

No I have not seen this. The last file you posted has the calculation settings to "Automatic" and after a run using "Engine:=2" calculation is still set to "Automatic"

I've not found any explanation to the "Objective Cell must be ..." message. Have used solver and VBA on a number of occasions but only seen this in this case

https://www.excelforum.com/excel-pro...ml#post5288597
but then it was a real pain in the .... as well as the Scandinavian date format when trying to help a forum member living in South Africa.

Alf

[zzzeddy]

Hi Alf

As far as I remember, Engine 1 is the Linear one, and Engine 2 is the GRG Nonlinear one, not the other way around.

..I mis-remembered. You are indeed correct, it's 1 for the Generalized Reduced Gradient GRG Engine, 2 for the Linear.

I managed to get rid of the "Objective Cell must be ..." by first setting a dummy Solver like this..

Please Login or Register  to view this content.

zeddy

[Alf]

Thanks for the info on how to get rid of "Objective Cell mist be ...".

Alf