I have data in Sheet Imported Data
I need code to extract data to sheet "Extract" based on a date range in AA2 and AB2 as well as a value AC3
I have manually extracted the data
It would be appreciated if someone could provide me with the code
I have data in Sheet Imported Data
I need code to extract data to sheet "Extract" based on a date range in AA2 and AB2 as well as a value AC3
I have manually extracted the data
It would be appreciated if someone could provide me with the code
VBA code not required. Formula will do.
In A2 then copied to full range.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thanks for the Help
I need the items that Sum up to the Value in AC2 that are within the date range in AA2 to AB2 in sheet "Imported Data" to be extracted on to sheet "Extract"
Kindly amend your formula
You mean Sum Of Column B should be exactly =AC2.
Last edited by kvsrinivasamurthy; 08-07-2022 at 06:11 AM.
That is Correct
The Value in this example in AC2 is 63656.41 and the date range to be extracted is in AA2 to AB2 (Sheet Imported data")
Where the value is equal to 63656.41 in this example and the date range is from 25/07/2022 to 01/08/2022 , then to extract all data that is equal to R63656.41 in the date range 25/07/2022 to 01/08/2022 on sheet "Extract"
i have manually extracted the data to show you what I am trying to achieve on sheet "Extract"
Hi kvsrinivasamurthy
Just following up whether you are able to provide a formula to extract the data as per post # 5 or is VBA required ?
Your assistance is most appreciated
It seems not simple enough for a formular based solution, also VBA even.
With VBA, it require combination of sub and function, that I have never worked with it before.
Quang PT
Thanks for your feedback bebo02199
Hopefully there will be someone on this forum who may be able to provide a suitable VBA solution
I changed the data so it meets the criteria.
After that you can use a pivot table to analyse the data.
The total of the criteria is 444.549,50.
You require 636.656,41 (which is more)
So there could not be a match for the given critieria.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thanks for the help Oeldere
I fully understanding your formulas. How do I now extract data summing up to my criteria value ?
Filter on column I on True.
Thanks, but the the Items indicated as True are for the date ranges and Plan TO , but does not take accounts that items summing up the a certain value for eg if I wanted only to show//extract those items summing up to say 67088.64, that relates to Finance and is bettern 25/07/2022 and 01/08/2022. I would like to be able to extract only those items summimg up to a specific values, where the Plan TO is "Finance" and for a specific date range
See the attached file.
In Row 5 the value is over the limit.
Of course there could be a formula to determine that criteria.
Try amending you formula to only show those items that sum up to a specific value for e.g. 65401.82 (in this example I selected B5, B6, B16) and for Finance and within range 25/07/2022 to 01/08/2022
I can't help you with that one.
Maybe solver could be a solutions.
With this dataset i suppose it could be done with VBA (and I can't help you with that one).
thanks for all your effort Oedere
I have used solver a lonmg time ago, but have forgotten how to use it
I found A Youtube tutorial on Solver. Its fairly simple but very slow
It appears that VBA may be the best solution
I've setup a VBA driven solver model. To run it a reference must be set to solver in VBA. Developers tab -> Click Visual basic icon -> Tools -> References and tick box "Solver".
Update run by activating sheet "Imported Data" and update start and stop date cells K1 and M1. Then run macro "FindSum".
Macro clears previous values from sheet "Extract". It sets an auto filter on sheet "Imported Data" and copies relevant data to sheet "Extract", writing the formula for target cell (Object function cell K1).
Macro then builds solver model by setting cell K1 to max and adds a constraint for cell K1 setting it to be equal to or less than the cell R2 value. It also specifies a range for binaries in the H column and then runs solver.
Finally, it sets an auto filter on the H column with criterial = 1 to find the values that adds up to the target value.
Alf
Thanks Alf. I entered the start and stop dates and entered the sum of the items in R2
I need to extract all rows where the values in Col B sum to a specified value in R2 based on a date range in K1 and M1
I then ran your macro and no transactions were extracted
Please test & advise
Last edited by Howardc1001; 08-09-2022 at 04:37 AM.
This happened because you ran the macro when the sheet "Extract" was the active sheet.
I also see that you have start date and stop date in sheet "Imported Data" cell AA2 (start date) and cell AB2 (stop date) as well as target sum AC2.Update run by activating sheet "Imported Data" and update start and stop date cells K1 and M1. Then run macro "FindSum".
Change this part of the macro
toPlease Login or Register to view this content.
Then you can keep on doing updating these parameters as well as the target sum the same way you have done before and clear the range J1:M1 as this will not be needed after the code change.Please Login or Register to view this content.
Alf
Hi Alf
Thanks for your reply. I have select the macro whilst on sheet "Imported Data" and no data is being extracted
In the example use date range from 26/07/2022 (Col D) to 01/08/2022 )Col D), Total amount 63335.98 (Col B) and "Finance" (Col G)
I have manually extracted the data to show you what I am trying to achieve
Please test and advise where I am going wrong
Did you set a reference to solver in Visual basic as I told you to do before you could run the macro "FindSum"
Looking at your solver:
your_solver.jpg
compairing it with mine it don't look like you did
My_solver.jpg
Here is where you set conncetion to solver in visual basic;
solver_ref.jpg
I also add you file run in my enviroment and an answar is found with two amounts.
Alf
And here is the uploaded fil I promised
Alf
Hi Alf
Many thanks for he help. I had Solver selected under references in VBA
However, I seem to be missing one step as I cannot get the macro to extract the data
Frankly I'm puzzled. Your uploaded file runs without any problem in my PC's environment Office 2019 for Windows. I also assume that your Office 2021 is a Windows version and not the MAC one as there earlier have been problems running solver with VBA in a MAC environment.
The only thing I can suggest now is that when you click on macro icon and instead of selecting "Run" select "Step Into" for the macro find "FindSum".
I would suggest you start with "Import Data" as the active sheet. Then press the F8 button till the yellow color is on the line "Sheets("Extract").Activate". The next time you press F8 this command will be executed so check in the excel environment that the active sheet is now "Extract". So you step trough the macro and check after every command that the previous one is executed in the excel environment to see if you can find the problem.
If you have access to another PC running Office 2010 or higher for Windows you could perhaps test this file on another PC in case there are some problem with your Office installation.
Alf
Hi Alf
It is rather puzzling that it works on your PC
I have stepped into the macro using F8
When I get to this section of the code , then sheet "Imported Data" is filtered showing no Data-have attached workbook
Please Login or Register to view this content.
The first lines in the macro after setting start and stop date clears the "Extract" sheet from previous values, the line "Sheets("Imported Data").Activate" will activate the "Imported Data" sheet and
"Range("D1").AutoFilter Field:=4, Criteria1:=">=" & startD, Operator:=xlAnd, Criteria2:="<=" & stopD" will build the autofilter on the "Imported Data" sheet with date values equal to or higher than start date and equal to or less than stop date.
The line "Range("A1").CurrentRegion.Copy" copies the filtered range and the next line pastes it to the "Extract" sheet.
Then the line "ActiveSheet.Range("A1").AutoFilter" removes the autofilter from sheet "Imported Data" and then the "Extract" sheet is activated and a column for binaries are defined.
k is used to find the last row number in he filtered range that was pasted to the "Extract" sheet.
A SUMPRODUCT formula then build i.e. where amount times binary values should give the target amount.
Then the solver model is build and run and the result is filtered to find binary values equal to 1.
Alf
The only other thing I can think of is a conflict with "Regional Settings". If you run "Control Panel" and click "Clock and Region" -> "Region" what region is specified as excel operates according to these settings.
You give your location as "South Africa" but there is no direct setting like this in the drop down panel but rather like "Sesotho (South Africa)" or "Setswana (South Africa)" but even if one lives in a specific country the regional setting my be quite different from that.
My regional setting is Swedish but my excel is set up in using the English language and has the international date setting i.e. yyyy-mm-dd. What is your date setting by the way?
Alf
Hi Alf
My setting is dd/mm/yyyy. As your is set to yyyy-mm-dd, it would in all likelihood have caused a conflict issue
Yes that was the problem. I set my formats to English (United Kingdom) and stepping through the macro as soon as the line
was executed the data range in sheet "Imported data" changed from this:Please Login or Register to view this content.
a_data.jpg
to this:
b_data.jpg
and then there are no data for the macro to work. well this is a problem that must be solvable. Will do some test to see if international setting on date will work, then macro could be modified to change setting for that particular range to international at the start of the run and then be changed normal UK format at the same time as the autofilter is removed.
Alf
Last edited by Alf; 08-10-2022 at 10:46 AM.
Many thanks for all your patience and effort in trying to get this resolved
Looking forward to receiving your updated solution
Testing with my Format setting English (United Kingdom) and searching the internet I do think I found a solution to your problem. Testing with your downloaded file the modified
autofilter setting seems to work.
But you better do a more throughout testing. Another thought that strikes me, will the imported data always be sorted with start date at top and latest date at bottom?
Should a sorting of dates be included in macro?
Alf
Last edited by Alf; 08-11-2022 at 04:16 AM.
Hi Alf
Many thanks for all the effort. The data extract perfectly
The data will always be imported with the start date at top and latest date at bottom, so no need for a macro to sort this
You are welcome and thanks for the feedback
Alf
Ps In your first post ->"Thread Tool" dropdown could you find "Solved" and mark this post so. As solving this problem took quite a bit of effort I would appreciate it.
It was a great effort on your part and I truly appreciate it
I have used your code with real live data and it works perfectly
Hi Howard
Thanks for rep and marking thread solved.
The solver target value is set to max with a constraint that it should be equal to or less than target value set in AC2. This means that solver will always find a solution but if it can't find a solution that matches exactly the set target value if will find the value that gives the smallest difference between target value and solver result.
The model is dynamic i.e. the size of the data is cheeked by the macro and the filtering range is set according to size. The solver model build on filtered data to sheet "Extract" is also dynamic and will be adjusted to the proper size found by the macro.
As a participant in this thread any postings done will send an email to me so if you have any problem with the file and macro post it in his thread.
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks