+ Reply to Thread
Results 1 to 36 of 36

Macro to extract data based on Date Range and Value

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Macro to extract data based on Date Range and Value

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Macro to extract data based on Date Range and Value

    VBA code not required. Formula will do.
    In A2 then copied to full range.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Macro to extract data based on Date Range and Value

    You mean Sum Of Column B should be exactly =AC2.
    Last edited by kvsrinivasamurthy; 08-07-2022 at 06:11 AM.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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"
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Macro to extract data based on Date Range and Value

    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

  8. #8
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    Thanks for your feedback bebo02199


    Hopefully there will be someone on this forum who may be able to provide a suitable VBA solution

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to extract data based on Date Range and Value

    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.

  10. #10
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    Thanks for the help Oeldere


    I fully understanding your formulas. How do I now extract data summing up to my criteria value ?

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to extract data based on Date Range and Value

    Filter on column I on True.

  12. #12
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to extract data based on Date Range and Value

    See the attached file.

    In Row 5 the value is over the limit.

    Of course there could be a formula to determine that criteria.

  14. #14
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to extract data based on Date Range and Value

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

  16. #16
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    thanks for all your effort Oedere

    I have used solver a lonmg time ago, but have forgotten how to use it

  17. #17
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    I found A Youtube tutorial on Solver. Its fairly simple but very slow

    It appears that VBA may be the best solution

  18. #18
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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

  19. #19
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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.

  20. #20
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    This happened because you ran the macro when the sheet "Extract" was the active sheet.

    Update run by activating sheet "Imported Data" and update start and stop date cells K1 and M1. Then run macro "FindSum".
    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.

    Change this part of the macro

    Please Login or Register  to view this content.
    to

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

    Alf

  21. #21
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  22. #22
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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

  23. #23
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    And here is the uploaded fil I promised

    Alf

  24. #24
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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
    Attached Images Attached Images
    Attached Files Attached Files

  25. #25
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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

  26. #26
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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.

  27. #27
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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

  28. #28
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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

  29. #29
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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
    Attached Images Attached Images

  30. #30
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    Yes that was the problem. I set my formats to English (United Kingdom) and stepping through the macro as soon as the line
    Please Login or Register  to view this content.
    was executed the data range in sheet "Imported data" changed from this:
    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.

  31. #31
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    Many thanks for all your patience and effort in trying to get this resolved

    Looking forward to receiving your updated solution

  32. #32
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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
    Attached Files Attached Files
    Last edited by Alf; 08-11-2022 at 04:16 AM.

  33. #33
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  34. #34
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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.

  35. #35
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract data based on Date Range and Value

    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

  36. #36
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to extract data based on Date Range and Value

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 15
    Last Post: 04-09-2021, 03:13 AM
  2. [SOLVED] Extract information based on multiple criteria and also date range
    By Punnam in forum Excel General
    Replies: 2
    Last Post: 05-20-2020, 09:51 AM
  3. [SOLVED] Macro to extract data based on date range and values
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 36
    Last Post: 05-08-2020, 10:44 AM
  4. Replies: 1
    Last Post: 04-06-2016, 07:02 AM
  5. Need macro or userform for Extract data from multiple workbooks by giving date range
    By hemant_sonawane in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-04-2014, 06:16 AM
  6. extract data by date range and place in different tabs based on name
    By Sway1978 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2013, 03:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1